Avatar of Rozamunda
Rozamunda
 asked on

SQL DB/2 for AS/400 question V.6.1 - OVER

Hi, i want to sort by calculated field and then use over to retrieve records within certain range, it works when i sorted by a field which is in the table, but not calculated:

SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY fld1,fld2,DIFF    
 ) as ROW, 3*fld1 as REV,fld2-3*fld1 as  
 DIFF                                                              
       FROM FILE1                                     
        WHERE fld4 =1 ) as ALL                                   
                                                                   
                                                                   
 WHERE ALL.ROW  BETWEEN 1 AND 1000                                 

Open in new window

IBM System iDB2Databases

Avatar of undefined
Last Comment
slinkygn

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
momi_sabag

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
slinkygn

Easiest way may be to create your search as a view without ROW_NUMBER() OVER(), and then do another SELECT on the view to get the ROW_NUMBER in there.  (Might also be able to do the same thing with a WITH clause, but I'm not so sure about that.)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck