>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