×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Whenever sqlerror continue does not work!!!

Whenever sqlerror continue does not work!!!

Whenever sqlerror continue does not work!!!

(OP)
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.

CODE

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

RE: Whenever sqlerror continue does not work!!!

what about something like this ?

CODE

...
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close