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.
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