Whenever sqlerror continue does not work!!!

[aHash]

Hi all,
I am trying to run a simple procedure that will do some history update based on the value stored in COL1 in the table TABLE1.

It says at on update statement There are some duplicates and could not update. I tried to do a "WHENEVER SQLERROR CONTINUE"; at the end of update statements but it wont accept and compile? What am I doing wrong?

Please advice and thanks.

CREATE PROCEDURE "informix".restore_unitchange ()
DEFINE P_UAD_OLD_UNIT CHAR(10);
DEFINE P_UAD_NEW_UNIT CHAR(10);
DEFINE P_UAD_SYSTEM CHAR(2);
DEFINE P_UAD_DATE DATE;
DEFINE P_COL1 CHAR(10);
DEFINE TEMP_KEY CHAR(10);

	FOREACH UNITCHANGE_CUR FOR
            SELECT COL1
              INTO P_COL1
              FROM TABLE1
    
    BEGIN

                IF LENGTH(P_COL1) = 1 THEN
                                       --123456789
                   LET TEMP_KEY = P_COL1 || '         '; 
                END IF;
                IF LENGTH(P_COL1) = 2 THEN
                       LET TEMP_KEY = P_COL1 || '        ' ;
                END IF;
                IF LENGTH(P_COL1) = 3 THEN
                        LET TEMP_KEY = P_COL1 || '       ';
                END IF;
                IF LENGTH(P_COL1) = 4 THEN
                        LET TEMP_KEY = P_COL1 || '      ' ;
                END IF;
                IF LENGTH(P_COL1) = 5 THEN
                        LET TEMP_KEY = P_COL1 || '     ' ;
                END IF;
                IF LENGTH(P_COL1) = 6 THEN
                        LET TEMP_KEY = P_COL1 +  '    ';
                END IF;
                IF LENGTH(P_COL1) = 7 THEN
                        LET TEMP_KEY = P_COL1 ||  '   ';
                END IF;
                IF LENGTH(P_COL1) = 8 THEN
                        LET TEMP_KEY = P_COL1 ||  '  ' ;
                END IF;
                IF LENGTH(P_COL1) = 9 THEN
                        LET TEMP_KEY = P_COL1 || ' ';
                END IF;
                IF LENGTH(P_COL1) = 10 THEN
                        LET TEMP_KEY = P_COL1;
                END IF;

    		    SELECT   
  				  UAD_OLD_UNIT,
				  UAD_NEW_UNIT,
                  UAD_DATE,
				  UAD_SYSTEM
			  INTO 
				  P_UAD_OLD_UNIT,
				  P_UAD_NEW_UNIT,
                  P_UAD_DATE,
				  P_UAD_SYSTEM
			  FROM 
				  UNCHGAUD			
            WHERE UAD_OLD_UNIT = P_COL1 AND
                  UAD_SYSTEM='NA' AND
                  SUBSTR(UAD_PRIME_KEY,1,8) = '20070521';

		IF SUBSTR(P_UAD_NEW_UNIT,1,3) = '[D]' AND P_UAD_SYSTEM ='NA' THEN

						UPDATE PROPMAST 
						   SET PM_ID_NO = P_UAD_OLD_UNIT , PM_DELETED=''
						 WHERE PM_ID_NO = P_UAD_NEW_UNIT AND
                               PM_DELETED = P_COL1;

						UPDATE PROPDET 
						   SET PRD_ID_NO = P_UAD_OLD_UNIT 
						 WHERE PRD_ID_NO = P_UAD_NEW_UNIT;

						UPDATE BILLDET 
						   SET BD_UNIT_NO = P_UAD_OLD_UNIT, 
                               BD_KEY =  TEMP_KEY || SUBSTR(BD_KEY,11,24)
						 WHERE BD_UNIT_NO = P_UAD_NEW_UNIT AND 
                               BD_SYSTEM = 'NA';

						UPDATE AWODET 
						   SET AW_UNIT_NO = P_UAD_OLD_UNIT 
						 WHERE AW_UNIT_NO = P_UAD_NEW_UNIT AND 
                               AW_SYSTEM = 'NA';

						UPDATE WOMAST 
						   SET WM_EM_UNIT_NO = P_UAD_OLD_UNIT 
						 WHERE WM_EM_UNIT_NO = P_UAD_NEW_UNIT AND 
                               WM_SYSTEM = 'NA';

						UPDATE WOXREF 
						   SET WX_EM_UNIT_NO = P_UAD_OLD_UNIT 
						 WHERE WX_EM_UNIT_NO = P_UAD_NEW_UNIT AND 
                               WX_SYSTEM = 'NA';

						UPDATE WOLABOR 
						   SET WL_EM_UNIT_NO = P_UAD_OLD_UNIT 
						 WHERE WL_EM_UNIT_NO = P_UAD_NEW_UNIT  AND 
                               WL_SYSTEM = 'NA';

						UPDATE WOPARTS 
						   SET WP_EM_UNIT_NO = P_UAD_OLD_UNIT 
						 WHERE WP_EM_UNIT_NO = P_UAD_NEW_UNIT  AND 
                               WP_SYSTEM = 'NA';

						UPDATE WOCOMM 
						   SET WC_EM_UNIT_NO = P_UAD_OLD_UNIT 
						 WHERE WC_EM_UNIT_NO = P_UAD_NEW_UNIT AND 
                               WC_SYSTEM = 'NA';

                     
						UPDATE HISTDET 
						   SET HD_UNIT_NO = P_UAD_OLD_UNIT ,
							   HD_UNIT_YEAR =  TEMP_KEY || SUBSTR(HD_UNIT_YEAR,11,16)
						 WHERE HD_UNIT_NO = P_UAD_NEW_UNIT  AND 
                               HD_SYSTEM = 'NA';
                      
         END IF;
	END;
          
   END FOREACH;

END PROCEDURE

 

[PHV]

what about something like this ?

...
BEGIN ON EXCEPTION END EXCEPTION WITH RESUME
  UPDATE PROPMAST 
     SET PM_ID_NO = P_UAD_OLD_UNIT , PM_DELETED=''
   WHERE PM_ID_NO = P_UAD_NEW_UNIT AND PM_DELETED = P_COL1;
END
BEGIN ON EXCEPTION END EXCEPTION WITH RESUME
  UPDATE PROPDET 
     SET PRD_ID_NO = P_UAD_OLD_UNIT
   WHERE PRD_ID_NO = P_UAD_NEW_UNIT;
END
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886