>HI Louis,
>
>It's pretty cool, but i need only in SQL, that too same number for all records
>for same employee and next incremental number for all records of the another
>employee.
>
>SET any help offered ?
The first thing that comes to mind, is EXECUTE BLOCK (which is basically a
STORED PROCEDURE built dynamically and not stored anywhere and that can be used
anywhere a SELECT can be used, at least for recent Fb versions) combined with a
temporary table, i.e.
EXECUTE BLOCK RETURNS (<output params>) AS
DECLARE VARIABLE RowNo INTEGER;
DECLARE VARIABLE EmpCode <Empcode type>;
BEGIN
RowNo = 1;
FOR SELECT DISTINCT EmpCode
FROM <your entire where clause> INTO :EmpCode DO
BEGIN
INSERT INTO MyTempTable(RowNo, EmpCode) VALUES (:RowNo, :EmpCode);
RowNo = RowNo + 1;
END
FOR SELECT <Whatever you want>
FROM MyTempTable MTT
JOIN <all your other tables>
WHERE <your entire where clause>
INTO <output params> DO
SUSPEND;
END
It might also be possible to actually create a stored procedure similar to:
CREATE PROCEDURE (MyID <whatever> RETURNS (RowNo Integer) AS
BEGIN
RowNo = SELECT RowNo
FROM MyTempTable
WHERE MyID = :MyID;
IF (RowNo IS NULL) THEN
BEGIN
RowNo = SELECT GetContext(SetContext(GetContext) --Don't have time now to
find out exactly how
INSERT INTO MyTempTable(RowNo, MyID) VALUES(:RowNo, :MyID);
END
END
Then you'd only use a LEFT JOIN to this stored procedure in your queries (this
could be reusable for different queries, not tied to your particular query).
HTH,
Set
