Re: SQL Proc and Data Not Found

["Wes" <small_du@xxxxxxxxx>]

Thanks Richard. That's exactly what I was looking for.

Wes

"Richard Casey" <casey_r@xxxxxxxxxxxxxxxx> wrote in
message news:DHEKJHMBPPGODHNBIOKIMEEMEJAA.casey_r@xxxxxxxxxxxxxxxxxxx
> Wes,
>
> In your WHILE loop that processes your cursor, you should have a FETCH
> before the END WHILE. Just set your at_end variable to zero before that
> FETCH. Any record not found condition from SELECTs within the WHILE loop
> will set at_end to 1. By setting at_end to 0 before the FETCH, the WHILE
> loop will only exit if the FETCH statement hit "end of file".
>
> Like so:
>
> DECLARE CONTINUE HANDLER FOR not_found
>     SET at_end = 1;                                               .
> .
> OPEN C1;
>
> FETCH C1 INTO  v_mcu, v_Emcu, v_Company_ID, v_Ord_#, v_Ord_Type,
>          v_TankID, v_an8, v_shan, v_Ord_Date, v_GL_Date,
>          v_Act_Ship_Date, v_Short_Item#, v_Item,
>          v_UOM, v_Ship_qty, v_Vol, v_unit_price ,
>          v_Ext_Amount, v_unit_cost, v_Ext_Cost, v_GLC;
> WHILE  at_end = 0   DO
>
>    SELECT  UDVMCU, UDTRP INTO v_Trip_Depo, v_Delivery_#
>     FROM  F49211
>    WHERE  UDDOCO = v_Ord_#
>      and  UDDCTO = v_Ord_Type
>      and  UDLNID = v_Line_ID
>      and  UDKCOO = v_Company_ID;
>
>    IF at_end = 1 THEN
>    .. what to do if record is NOT found in F49211
>    ELSE
>    .. what to do if record IS found in F49211
>    END IF;
>
>    SET at_end = 0;
>
>    FETCH C1 INTO  v_mcu, v_Emcu, v_Company_ID, v_Ord_#, v_Ord_Type,
>             v_TankID, v_an8, v_shan, v_Ord_Date, v_GL_Date,
>             v_Act_Ship_Date, v_Short_Item#, v_Item,
>             v_UOM, v_Ship_qty, v_Vol, v_unit_price ,
>             v_Ext_Amount, v_unit_cost, v_Ext_Cost, v_GLC;
>
> END WHILE;
>
>
> Hope this helps!
> Richard
>
>
> -----Original Message-----
> From:
midrange-l-bounces+casey_r=popmail.firn.edu-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxx
ane.org
>
[mailto:midrange-l-bounces+casey_r=popmail.firn.edu-Zwy7GipZuJhWk0Htik3J/w@p
ublic.gmane.org]On
> Behalf Of Wes
> Sent: Monday, January 31, 2005 11:59 AM
> To: midrange-l@xxxxxxxxxxxx
> Subject: SQL Proc and Data Not Found
>
>
> I'm writing a proc where I have a cursor and for each row found in the
> cursor I do a select from other files. I'm monitoring for record not found
> conditions for the cursor. My question is how do I trap rows not found for
> the select statements other than the cursor? I don't want my program to
> exits just because it can't find a record in the select statement.
>
>
>
> DECLARE CONTINUE HANDLER FOR not_found
>     SET at_end = 1;                                               .
> .
> .
> .
> OPEN C1;
>
> FETCH C1 INTO  v_mcu, v_Emcu, v_Company_ID, v_Ord_#, v_Ord_Type,
>          v_TankID, v_an8, v_shan, v_Ord_Date, v_GL_Date,
>          v_Act_Ship_Date, v_Short_Item#, v_Item,
>          v_UOM, v_Ship_qty, v_Vol, v_unit_price ,
>          v_Ext_Amount, v_unit_cost, v_Ext_Cost, v_GLC;
> WHILE  at_end = 0   DO
>   -- SET DATE
>
>    SELECT  UDVMCU, UDTRP INTO v_Trip_Depo, v_Delivery_#
>     FROM  F49211
>    WHERE  UDDOCO = v_Ord_#
>      and  UDDCTO = v_Ord_Type
>      and  UDLNID = v_Line_ID
>      and  UDKCOO = v_Company_ID;
>
>
>
>
> Thanks, Wes
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>