Actually not hard to understand if you break it down a little and just rewrite
it a bit
Here is the original
select
cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer) as row_number,
rdb$set_context('USER_TRANSACTION', 'row#',
coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0) + 1),
< other columns>
from MY_TABLE
so
the while section
coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0)
is purely there to cast the get_context to integer and then coalesce it to zero
for the first row when the context does not yet exist. Let’s replace the 2
instances with GET_ROW_NUM for clarity
therefore
select
GET_ROW_NUM as row_number,
rdb$set_context('USER_TRANSACTION', 'row#',
GET_ROW_NUM + 1),
< other columns>
from MY_TABLE
but the second GET_ROW_NUM is actual part of the set_context so we just move it
select
GET_ROW_NUM as row_number,
rdb$set_context('USER_TRANSACTION', 'row#',GET_ROW_NUM + 1),
< other columns>
from MY_TABLE
and replace (for clarity) set_context(scope,name,value) with SET_ROW_NUM(value)
then it becomes
select
GET_ROW_NUM as row_number,
SET_ROW_NUM(GET_ROW_NUM + 1),
< other columns>
from MY_TABLE
this makes it much simpler to read.
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: 13 March 2015 06:44 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How To Generate Auto Increment Number in SQL
Itself ?
On Mar 13, 2015, at 8:45 AM, Vishal Tiwari vishuals...@yahoo.co.in
[firebird-support] <firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com> > wrote:
I have EmpCode column in my SQL
Say for example, my sql returns total 100 records in that it returns 10 records
for every employee. Here I would I display Row_Number as 1 for first 10 records
i.e. for first employee, then for next 10 records i.e. for second employee
Row_Number as 2, and so on. So finally Row_Number value should endup with 10.
How about a second context variable for the EmpCode and reset the row_number
iff the EmpCode changes? Or, if you'd rather number each row for an employee
differently and start again at 1 for the next employee, increment the
row_number unless EmpCode changes then reset to 1.
Sigh. SQL used to be such a simple ugly stupid language.... Am I the only
one who finds this sort of magic unreadable?
Cheers,
Ann
[Non-text portions of this message have been removed]
