how to use for statement in an sql query, I always got sqlstate: 42601

[antonio]

The query below never works when executed from DB2 9.5 control center

FOR v_row AS SELECT label FROM component
DO
update gateway_authentication set serial_number='yoyo';
END FOR;

How to write a loop query with DB2 ?

It is really difficult to have an example that works on the web !!!

thanks a lot for an answer

Antonio

[Serge Rielau]

FOR is not an SQL statement. It's an SQL PL statement.
So you need to put it inside of a compound statement.
In DB2 9.5 you have to use "compound SQL (dynamic)"

BEGIN ATOMIC

FOR v_row AS SELECT label FROM component
DO
update gateway_authentication set serial_number='yoyo';
END FOR;

END

Cheers
Serge

PS: I'm assuming this is a nonsense example.. so I'll resist tuning it
etc....
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

[antonio]

Serge Rielau a �crit :

> FOR is not an SQL statement. It's an SQL PL statement.
> So you need to put it inside of a compound statement.
> In DB2 9.5 you have to use "compound SQL (dynamic)"
>
> BEGIN ATOMIC
> FOR v_row AS SELECT label FROM component
> DO
> update gateway_authentication set serial_number='yoyo';
> END FOR;
> END
>
> Cheers
> Serge
>
> PS: I'm assuming this is a nonsense example.. so I'll resist tuning it
> etc....

Thanks a lot for the advice
I'm going to follow this and test

Antonio