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 vishualsoft@... [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]