Search code examples
sqldb2row-number

using sql variable in fetch statement in db2

DECLARE N_COUNT integer;
set N_COUNT = (select statement returning a single value)

Declaring a variable with integer datatype and setting a variable with a value.

select * from table1 fetch first (variable) rows only.

Now, I need to use the ncount variable in the select statement. I tried to use the variable but returned with an error. How will be able to achieve it?

Solution

  • The FETCH FIRST ROWS ONLY is an optimization feature, it does not accept variables.

    You will need to use ROW_NUMER() filtering:

     SELECT * FROM (
 select table1.*,
 row_number() over() as rownum
 from table1 
 ) AS t
 where rownum <= :n_count