Hi all,
I have the need to implement something like a row number and read the last thread about the autoincrement number which also led me to this FAQ:
http://www.firebirdfaq.org/faq343/

 

I now tried something like this:

select
cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as row_number,
rdb$set_context('USER_TRANSACTION', 'row#2',
coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0) + 1) as SET_CONTEXT,
<other columns>
from mytable

Which works fine. The first thing I now did is to create a derived table to get rid of the SET_CONTEXT part. I now experienced something very strange to me.

When I did this:

select s.* from
(select
cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as row_number,
rdb$set_context('USER_TRANSACTION', 'row#2',
coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0) + 1) as SET_CONTEXT,
<other columns>
from mytable) s

all is fine and row numbers are fine.

But when I explicitely call the individual rows like this;

select
s.row_number,
s.<other columns>
from
(select
cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as row_number,
rdb$set_context('USER_TRANSACTION', 'row#2',
coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0) + 1) as SET_CONTEXT,
<other columns>
from mytable)

then the row numbers have all the same value namely the last counter position (highest last value).

Am I doing something wrong?

Kind regards Christian