Hi,

At November 10, 2006, 5:19 PM, Jason Wharton wrote:

> I was not aware of this feature in FB2.

> Why does Firebird 2.0 use the FOR token here?

It's part of the SQL-2003 standard in relations to the sequence
support.

Here's the SQL-2003 standard section that describes it:

[Start quote ....]
6.13 <next value expression>

Function:
Return the next value of a sequence generator.

Format:
<next value expression> ::= NEXT VALUE FOR <sequence generator name>

Syntax Rules:

1) A <next value expression> shall be directly contained in one of the
following:

a) A <select list> simply contained in a <query specification> that
constitutes a <query expression> that is immediately contained in
one of the following:
i) A <cursor specification>.
ii) A <subquery> simply contained in an <as subquery clause> in
a <table definition>.
iii) A <from subquery>.
iv) A <select statement: single row>.
b) A <select list> simply contained in a <query specification> that
is immediately contained in a <dynamic single row select statement>.
c) A <from constructor>.
d) A <merge insert value list>.
e) An <update source>.
2) <next value expression> shall not be contained in a <case
expression>, a <search condition>, an <order by clause>, an
<aggregate function>, a <window function>, a grouped query, or in a
<query specification> that simply contains the <set quantifier> DISTINCT.

Access Rules:

1) Case:
a) If <next value expression> is contained in a <schema definition>,
then the applicable privileges for the <authorization identifier>
that owns the containing schema shall include USAGE privilege on
the sequence generator identified by <sequence generator name>.
b) Otherwise, the current privileges shall include USAGE privilege
on the sequence generator identified by <sequence generator
name>.
NOTE 104 — “applicable privileges” and “current privileges” are
defined in Subclause 12.3, “<privileges>”.

General Rules

1) If <next value expression> NVE is specified, then let SEQ be the
sequence generator descriptor identified by the <sequence generator
name> contained in NVE.

Case:
a) If NVE is directly contained in a <query specification> QS, then
the General Rules of Subclause 9.21, “Generation of the next
value of a sequence generator”, are applied once per row in the
result of QS with SEQ as SEQUENCE. The result of each evaluation
of NVE for a given row is the RESULT returned by the General
Rules of Subclause 9.21, “Generation of the next value of a
sequence generator”.
b) If NVE is directly contained in a <contextually typed table
value constructor> TVC, then the General Rules of Subclause
9.21, “Generation of the next value of a sequence generator”,
are applied once per <contextually typed row value expression>
contained in TVC. The result of each evaluation of NVE for a
given <row value expression> is the RESULT returned by the
General Rules of Subclause 9.21, “Generation of the next value
of a sequence generator”.
c) If NVE is directly contained in an <update source>, then the
General Rules of Subclause 9.21, “Generation of the next value
of a sequence generator”, are applied once per row to be updated
by the <update statement: searched> or <update statement:
positioned>. The result of each evaluation of NVE for a given
row is the RESULT returned by the General Rules of Subclause
9.21, “Generation of the next value of a sequence generator”.

Conformance Rules:
1) Without Feature T176, “Sequence generator support”, conforming SQL
language shall not contain a <next value expression>.
[...End quote]

--
Best regards,
Daniel Rail
Senior Software Developer
ACCRA Solutions Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)