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

In trying to get more of an understanding of Louis' solution, I tried something like you suggest. Parts of the time it worked OK, parts of the time I only got <null> (more specifically, I didn't manage to avoid nulls when trying to use the set_- and get_context inside a CTE), and at least once I got an error message saying something like 'too many context variables' (when trying to use the individual EmpCode's as individual context variables). Hence, I concluded it would take me a while to properly understand what Louis' did and that modifying it so that each distinct value in a table gets it's own context variable may fail if there are many distinct values.

>Sigh. SQL used to be such a simple ugly stupid language.... Am I the only one who finds this sort of magic unreadable?

I find it clever, but - like you - difficult to comprehend. Though in my case it could be due to me not learning new features of Firebird (I've never used get_context before)

Though I can think of other alternatives that may be simpler (although not as slick):

a) Works OK, but has to be ordered by EmpCode (or at least, I think it has to):

EXECUTE BLOCK RETURNS(RowNo INTEGER, EmpCode ....)
AS
DECLARE VARIABLE OldEmpCode ...
BEGIN
RowNo = 0;
FOR SELECT EmpCode... ORDER BY EmpCode... INTO :EmpCode... DO
BEGIN
IF (EmpCode IS DISTINCT FROM OldEmpCode) THEN
BEGIN
RowNo = RowNo + 1;
OldEmpCode = EmpCode;
SUSPEND;
END
END
END

b) Use a temporary table (need to have created a global temporary table in advance) and a separate SELECT in the same transaction or connection (depending on whether the temporary table is ON COMMIT PRESERVE ROWS or not) as the EXECUTE BLOCK:

EXECUTE BLOCK AS
DECLARE VARIABLE RowNo Integer;
DECLARE VARIABLE EmpCode ...
BEGIN
RowNo = 1;
FOR SELECT DISTINCT EmpCode FROM ... INTO :EmpCode DO
BEGIN
INSERT INTO MyTemporaryTable(RowNo, EmpCode) VALUES (:RowNo, :EmpCode);
RowNo = RowNo + 1;
END
END

SELECT MTT.RowNo, ...
FROM MyTemporaryTable MTT
JOIN ... ON MTT.EmpCode = ...

I think b) should be a pretty flexible solution that would be fairly simple for other programmers to comprehend (I prefer writing SQL that can easily be changed by another programmer over writing clever SQL that works beautifully, but it difficult to maintain).

The EXECUTE BLOCK of b) could have been replaced by a simple INSERT if Firebird had supported TEMPORARY SEQUENCES, but as far as I know, it doesn't.

Once, I also used a recursive CTE for such numbering, but that is both difficult to understand and cannot return many RowNo's, and I wouldn't recommend it.

Set