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