ROW_NUMBER() in DB2

Question

How to use ROW_NUMBER() in where clause in DB2 database. I have tried below but it did not work:

SELECT * FROM CSPAPP.LOCATIONS
WHERE (ROW_NUMBER() OVER(ORDER BY LOCATION)) BETWEEN 100 AND 200

It gave error : Invalid use of aggregate function or OLAP function.

I also tried with followiong ways :

SELECT (ROW_NUMBER() OVER(ORDER BY LOCATION)) AS RN ,* FROM CSPAPP.LOCATIONS
WHERE RN < 200

SELECT (ROW_NUMBER() OVER(ORDER BY LOCATION)) AS RN ,LOCATION FROM CSPAPP.LOCATIONS
WHERE RN < 200

Answer 1

You can't reference an alias on the same level where it is defined. You need to wrap this into a derived table:

SELECT location
FROM (
   SELECT row_number() over(order by location) as rn, 
          location 
   FROM cspapp.locations
)   
WHERE rn < 200

Answer 2

I use something like this when selecting based on row number in iSeries DB2:

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY location) as RRN, *
    FROM CSPAPP.LOCATIONS
    )
WHERE RRN between 100 and 200

If you are only interested in the 1 field you may be able to assign a name to the select and reference the fields:

SELECT DATA.location
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY location) as RRN, *
    FROM CSPAPP.LOCATIONS
    ) as DATA
WHERE DATA.RRN between 100 and 200