> 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
like

>
> 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
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com