CODEWITHSUNDEEP
sqldb2rownum
zod
zod

Reputation: 12417

How to fix "function not allowed" when using row_number() over a view?

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?

Upvotes: 2

Views: 305

Answers (2)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

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

Upvotes: 2

GregM
GregM

Reputation: 2654

You should include the row_number() in your view

Upvotes: 2

Related Questions