RE: SQL Proc and Data Not Found

["Richard Casey" <casey_r@xxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxx
[mailto:midrange-l-bounces+casey_r=popmail.firn.edu@xxxxxxxxxxxx]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.