using sql variable in fetch statement in db2

Question

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?

Possible duplicate of [Paramertize Fetch First n Rows Only in DB2](https://stackoverflow.com/questions/3751884/paramertize-fetch-first-n-rows-only-in-db2) — Stavr00, Nov 15 '19 at 15:42
It's not clear what you are trying to achieve. Do you want to assign an integer variable `n_count` a set of integer values? It's not possible. Can you describe the goal in other words using some example with test data? — Mark Barinstein, Nov 15 '19 at 16:40

score 0 · Answer 1 · answered Nov 15 '19 at 13:06

0

You can do this as below.

DECLARE @N_COUNT int;
SELECT @N_COUNT = ColumnName FROM TableName Where Condtion

Note: your query should return only single value of data type integer.

answered Nov 15 '19 at 13:06

Rizwan Ali Sabir

468
1
7
18

Stavr00 · Accepted Answer · 2019-11-15T15:53:44.190

0

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

edited Nov 15 '19 at 15:53

answered Nov 15 '19 at 15:47

Stavr00

3,219
1
16
28

using sql variable in fetch statement in db2

2 Answers2