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

Rozamunda
Rozamunda used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try this

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

Commented:
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.)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial