Harri,

Your problem is that if you want multiple statements to execute within
the FOR SELECT ... DO loop you need to include the statements with a
BEGIN ...END block.

Your statement would look something like...

FOR
SELECT ... INTO ...
DO
BEGIN
SELECT ... INTO ...;
P_ID = ...;
IF (P_ID > 0) THEN
SUSPEND;
END


Sean

> -----Original Message-----
> From: harri@... [mailto:harri@...]
> Sent: Saturday, January 27, 2001 12:50 PM
> To: ib-support@yahoogroups.com
> Subject: [ib-support] SP syntax question
>
>
> Hi!
>
> Is following legal in SP-s? I mean is it allowed
> to use "select" after For .. DO and to use suspend?
> I always get only 1 row when using SP, what is very
> similar to this.
> If this should work, then I continue the search for bug ...
>
> thanks,
> Harri
>
> --------
>
> CREATE PROCEDURE T ( ID1 INTEGER )
> RETURNS ( P_ID INTEGER )
> AS
> DECLARE VARIABLE POS1 INTEGER;
> DECLARE VARIABLE POS2 INTEGER;
> ...
> BEGIN
> P_ID = 0;
> if ( ID = 1) then
> begin
> select Max(A) from B1 where C = 0 INTO :POS1;
> select Max(A) from B2 where C = 0 INTO :POS2;
> /* more similar queries */
> FOR select X from Y where (Z1 = :POS1) and (Z2 = :POS2)
> INTO :var1
> DO
> /* if I put "suspend" here, then I get multiple results
> so there must be something wrong with following */
> select I from J1 where K = :var1 INTO :var2;
> P_ID = var2 - 4;
> if (P_ID > 0 ) then
> suspend; /* this should return P_ID if it is > 0 */
>
> end
> else
> begin
> select A from B1 where C > 0 INTO :POS1;
> select A from B2 where C > 0 INTO :POS2;
> /* more similar queries */
> FOR select X from Y where (Z1 = :POS1) and (Z2 = :POS2)
> INTO :var1
> DO
> select I from J1 where K = :var1 INTO :var2;
> select I from J2 where K = :vari INTO :var3;
> P_ID = var1 + var2;
> if (P_ID > 0 ) then
> suspend; /* this should return P_ID if it is > 0 */
>
> end
>
>
> ------------------------ Yahoo! Groups Sponsor
> ---------------------~-~>
> eGroups is now Yahoo! Groups
> Click here for more details
> http://click.egroups.com/1/11231/0/_/_/_/980617781/
> --------------------------------------------------------------
> -------_->
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>