>>>> I wish to display the ID (of all time points = 0). I can handle this programatically but rather would do in SQL.
>>>> The following query returns the time points
>>>>
>>>> SELECT ID as VAL
>>>> FROM x
>>>> WHERE "TIME" = 0
>>>> order by id
>>>>
>>>> The values are
>>>> 87
>>>> 117
>>>> 120
>>>>
>>>> Now, is there a function in FB that will return me an ID (sequential, starting at 1 and increments of 1) for each of the records like so?
>>>>
>>>> ID VAL
>>>> 1 87
>>>> 2 117
>>>> 3 120
>
>>> Although it might be possible with a mix of RDB$SET_CONTEXT,
>>> RDB$GET_CONTEXT, it's rather easy with a selectable stored procedure.
>
>> Thank you Thomas.
>>
>> I am not sure I am following. Could you possibly provide some more information on how this could work?
>
> This is to a certain extent doable in a select statement, but the select statement would not be simple. What Thomas said, was that it was simple if you wrote a stored procedure to do it (google or look at the Release Notes to see examples). Though you do not have to use a stored procedure with newer Firebird versions, you can achieve much of the same by using EXECUTE BLOCK:
>
> EXECUTE BLOCK RETURNS (ID INTEGER, VAL INTEGER)
> AS
> BEGIN
> ID = 0;
> FOR SELECT ID
> FROM x
> WHERE "TIME" = 0
> ORDER BY ID
> INTO :VAL
> DO
> BEGIN
> ID = ID + 1;
> SUSPEND;
> END
> END
>
> I don't know how the RDB$SET_CONTEXT and RDB$GET_CONTEXT that Thomas suggested works, but I suspect the above EXECUTE BLOCK is considerably simpler than using these. What I do know, is that you can achieve a similar result to EXECUTE BLOCK by using a recursive CTE, but that has some drawbacks in that it is complex, resource consuming and only works if you're selecting a limited number of records (I'm not sure whether it is 1000 or 32000).

The fun side of Firebird ;-)

select
rdb$set_context('USER_TRANSACTION', 'seq_no',
cast(coalesce(rdb$get_context('USER_TRANSACTION', 'seq_no'), 1) as
integer) + 1) as hidden_field
, coalesce(rdb$get_context('USER_TRANSACTION', 'seq_no'), 1) as id
, c.*
from
country c


The increment happens in context of the current transaction. If you
re-run the statement in the same transaction, ID won't start at 1 again.

I use that approach in some situation where I was tired to write an
EXECUTE BLOCK / PSQL thing for each use case.

HTH.

--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!