How do i declare and increment local variables in

Question

I want to show row number for each of result set row, I have this query in mySQL

SELECT @rownum := @rownum + 1 row, e.* FROM Employee e, (SELECT @rownum := 0) r

Here @rownum is local variable and would increment its value for each result row. How do i write this query in db2 ( ibm's dashdb ) ?

Answer 1

If you are looking to set a variable and set a value:

db2 -td@ "begin declare test integer; set test = 1; end @"

Or

begin
  declare test integer;
  set test = 1;
  set test = test + 1;
end @

Answer 2

If you're just looking to number the output rows, you can use the row_number() function:

select 
    row_number() over() as row, 
    e.* 
from 
    Employee e