|Subject||RE: [firebird-support] How To Generate Auto Increment Number in SQL Itself ?|
|Author||Svein Erling Tysvær|
>HI Louis,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.
>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 ?
EXECUTE BLOCK RETURNS (<output params>) AS
DECLARE VARIABLE RowNo INTEGER;
DECLARE VARIABLE EmpCode <Empcode type>;
RowNo = 1;
FOR SELECT DISTINCT EmpCode
FROM <your entire where clause> INTO :EmpCode DO
INSERT INTO MyTempTable(RowNo, EmpCode) VALUES (:RowNo, :EmpCode);
RowNo = RowNo + 1;
FOR SELECT <Whatever you want>
FROM MyTempTable MTT
JOIN <all your other tables>
WHERE <your entire where clause>
INTO <output params> DO
It might also be possible to actually create a stored procedure similar to:
CREATE PROCEDURE (MyID <whatever> RETURNS (RowNo Integer) AS
RowNo = SELECT RowNo
WHERE MyID = :MyID;
IF (RowNo IS NULL) THEN
RowNo = SELECT GetContext(SetContext(GetContext) --Don't have time now to find out exactly how
INSERT INTO MyTempTable(RowNo, MyID) VALUES(:RowNo, :MyID);
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).