Row_number - Sql0255

arammal · #1 (**permalink**) 06-16-09, 05:32

Hello,

I am working on a AS400 V5R4 machine, I have a problem with this sql querry :

Code:

SELECT ROW_NUMBER() OVER(), TAB.* FROM (
    SELECT COL_A, COL_B, COL_C 
    FROM TAB_ABC
    WHERE COL_B = 'TOTO'
    ORDER BY COL_A, COL_B ) TAB

it gives an SQL0255 error
Does anyone have any idea to make this querry run..

thank you,

sathyaram_s · #2 (**permalink**) 06-16-09, 05:59

Have you tried alternatives - so that the query can avoid the reason codes.

a) Move the ORDER BY to the OVER () part
b)

Code:

 SELECT ROWNUMBER() OVER ( ORDER BY COLA,COLB), COLAA, COLB,COLC FROM TAB_ABC
    WHERE COL_B = 'TOTO'

arammal · #3 (**permalink**) 06-16-09, 06:56

thank you sathyaram_s for your answer..

in fact the main querry:

Code:

    SELECT COL_A, COL_B, COL_C 
    FROM TAB_ABC
    WHERE COL_B = 'TOTO'
    ORDER BY COL_A, COL_B

is generated automatically by an external program..
what i am trying to do is paginating over this query by using
ROW_NUMBER() OVER() AS RN...WHERE RN BETWEEN 1 AND 20

Internet Services

Web Development

Digital Marketing

Consumer Tech