2
select * from       (                                                   
select a.*,row_number() over() as rk                                    
from table1 tba  
                     ) as foo where rk between 11 and 20

This works for database table. I am using a view which is a join of 2 tables. When i try to do rownum it is saying: "Function not allowed"

select * from       (                                                   
select a.*,row_number() over() as rk                                    
from view1 v1  
                     ) as foo where rk between 11 and 20

Any suggestion or alternative for rownum in DB2?

zod
  • 12,092
  • 24
  • 70
  • 106

2 Answers2

2

You should include the row_number() in your view

GregM
  • 2,634
  • 3
  • 22
  • 37
2

The other alternative to including it in the view, as @GregM suggested, is to put it in a CTE: 

WITH numbered as (SELECT a.*, ROW_NUMBER() OVER() as rk
                  FROM table1 as a)
SELECT *
FROM numbered
WHERE rk BETWEEN 11 AND 20

Please note that this is about as slow as your original code (which runs just fine on my version).

Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45