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/ 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
