Subject Re: [firebird-support] INTO clause is execute before SELECT
Author Martijn Tonies
> 2. The SELECT....INTO construct is an assignment statement that takes an
*ordered list* of values and assigns them one-by-one to an ordered list of
plain old variables. So, if you have a statement that is extracting a row
> select cola, colb, colc from aTable
> ...
> into :cola, :colb, :colc
> then cola is assigned first and colc is assigned last. And you're really
going to get into a muddle if you do
> select cola, colb, colc from aTable
> ...
> into :colb, :colc, :cola

Totally different and has nothing to do with this case :-)

> (I hope you can see that...)
> Now, apply this to your "problem"
> Assume
> acumunits = 0;
> acumprice = 0;
> select :acumunits + units,
> (:acumunits*:acumprice+price*units)/(:acumunits+units)
> from inventorytable
> where inventoryid=:inventoryid
> into :acumunits, :acumprice;
> The values to be passed into the set of variables in the INTO clause does
not happen by somehow dumping one set straight into the other. The
assignments are one-by-one in order.
> So --
> The first assignment that happens is:
> acumunits (variable in the INTO clause) = acumunits (variable, value 0) +
units (value from field units in the SELECT array), i.e. the variable
acumunits now has the value of the field units and the value 0 is gone.
> The second assignment
> acumprice=(acumunits*acumprice+price*units)/(acumunits+units)
> uses the current value of the variable acumunits when it calculates the
expression for the value into acumprice.

As I discussed on ICQ, the "issue", which I now understand, is that
instead of the engine creating a resultset row, it doesn't "ready" such
a row, but readies each column on a per INTO-clause assignment.

(is this proper English?)

To me, this was a bit of a surprise as I expected the the engine to
create a resultset row and do the assignments afterwards.

As you can see, the original poster thought the same.

Alas, this is not the case, and no problem if you know it :-)

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
My thoughts:
Database development questions? Check the forum!