What is the actual problem? This procedure looks like it should work
fine (if some missing variable colons are added). I can't think of a
better way to do this.

Stan Walker



--- In IBObjects@yahoogroups.com, "John Costanzo" <strtline@a...>
wrote:

> Hello, I realize this is not an IBO problem but thought with all

the

> SQL people in the group it mught be a good place to ask this

question.

>
> I have the following situation. I need to loop through a table,
> select matching rows from another table based on a column in the

1st

> table, then update some colukmns in the 1st table based on
> calculations. This is what I cam up with:
>
> CREATE PROCEDURE TEST
> AS
> DECLARE VARIABLE COLAVAR SMALLINT;
> DECLARE VARIABLE COLBVAR NUMERIC(18,2);
> DECLARE VARIABLE COLCVAR NUMERIC(18,2);
> BEGIN
> FOR SELECT COLA
> FROM TABLE1
> INTO COLAVAR
> DO
> BEGIN
> SELECT SUM(COLB),Sum(COLC)
> FROM TABLE2
> WHERE (COLA = :COLAVAR)
> INTO :COLBVAR,:COLCVAR;
> UPDATE TABLE1
> SET COLX = :COLBVAR - COLCVAR
> WHERE COLA = :COLAVAR;
> END
> END #
>
> The FOR SELECT works fine. The SELECT SUM works fine. The problem

I

> have is with the UPDATE. It seems to me that since I am already
> in a FOR SELECT loop there should be a way to update the currently
> selected row without the need to reselect. The documentation shows

a

> statement WHERE CURRENT OF cursor. This seems what I need but It
> gives me an error. Is there another way or is the way I am doing

it

> correct. I am using Delphi7, Firebird, and IBO. Thanks in advance.
> Geno