Amit_Pekamwar
asked on
getting error - SQLSTATE=42601 in UDB
While compiling below ( in code section) sp I am getting below error -
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token ";" was found following "_cur; COMMIT; END P1".
Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=160.
SQLSTATE=42601
Can any body help to resolve above error. I have gone through multiple times through code but no findings.
Note:
close service_alert_cur; ---is line number 160
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token ";" was found following "_cur; COMMIT; END P1".
Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=160.
SQLSTATE=42601
Can any body help to resolve above error. I have gone through multiple times through code but no findings.
Note:
close service_alert_cur; ---is line number 160
SET SCHEMA = FCMCORE@
--DROP PROCEDURE FCMCORE.EXTRACT_VRU@
CREATE PROCEDURE FCMCORE.EXTRACT_VRU ( IN TS TIMESTAMP )
SPECIFIC EXTRACT_VRU
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
P1: BEGIN ATOMIC
--- Start variables for fields used in cursor sercice_alert_cur
DECLARE V_ACCT_CMS_CARD_ORG CHARACTER(3) DEFAULT NULL;
DECLARE V_ACCT_CMS_CARD_LOGO CHARACTER(3) DEFAULT NULL;
DECLARE V_ACCT_MULTI_ORG_ID INT;
DECLARE V_ACCT_CMS_OWNING_branch CHARACTER(9) DEFAULT NULL;
DECLARE V_ACCT_CMS_HOME_PH_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_WORK_PH_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_CH_1_MOB_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_HOME_PH_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_WORK_PH_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_CH_1_MOB_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_ADDR_POSTAL_CODE CHARACTER(9) DEFAULT NULL;
DECLARE V_ALERT_HWM_FRAUD_SCORE INT;
DECLARE V_ALERT_QUEUE_ID INT;
DECLARE V_ALERT_ACCT_NUM CHARACTER(20) DEFAULT NULL;
DECLARE V_ALERT_ALERT_ID INT;
DECLARE V_ALERT_ALERT_STATUS CHARACTER(10) DEFAULT NULL;
--- End variables for fields used in cursor sercice_alert_cur
--- Start variables for fields used in cursor checkout_cur
DECLARE V_chkout_HTS_ACCT_NUM CHARACTER(20);
DECLARE V_chkout_HTS_QUEUE_ID INT DEFAULT NULL;
DECLARE V_chkout_HTS_ALERT_STATUS CHARACTER(1);
DECLARE V_chkout_HTS_REQUEST_COUNTER INT DEFAULT NULL;
DECLARE V_chkout_HTS_CREATE_TIMESTAMP TIMESTAMP DEFAULT NULL;
DECLARE V_chkout_HTS_UPDT_TIMESTAMP TIMESTAMP DEFAULT NULL;
DECLARE V_TIME_DIFFERENCE INT;
--- End variables for fields used in cursor checkout_cur
DECLARE V_CHECKOUT_CNT INT;
DECLARE VAR_CURRENT_TIMESTAMP TIMESTAMP DEFAULT NULL;
DECLARE V_INSERT_CHECKOUT_FLAG int DEFAULT 0;
DECLARE at_end int default 0;
DECLARE at_end1 int default 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE dup_rec CONDITION FOR SQLSTATE '23505';
DECLARE dup_flag int default 0;
DECLARE not_found1 CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SET at_end=1;
DECLARE CONTINUE HANDLER FOR dup_rec
SET dup_flag=1;
delete from fcmcore.hts_dialer_out;
DECLARE sercice_alert_cur CURSOR for
Select b.cms_card_org,b.cms_card_logo,b.multi_org_id,b.cms_owning_branch,
b.CMS_HOME_PHONE_1,b.CMS_WORK_PHONE_1,b.CMS_CH_1_MOBILE_1,
b.CMS_HOME_PHONE_2,b.CMS_WORK_PHONE_2,b.CMS_CH_1_MOBILE_2,
b.CMS_ADDR_POSTAL_CODE,a.hwm_fraud_score,a.queue_id,a.account_num,
a.alert_id,a.alert_status from fcm_alert a,fcm_account b
Where
-- a.QUEUE_ID= Business will provide the queue ids and
a.Result_id is null and
a.ALERT_STATUS='ACTIVE' and
a.ACCOUNT_NUM=b.CMS_ACCT_NUM;
-- Opening service alert cusror
open service_alert_cur;
fetch service_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT_CMS_CARD_LOGO,V_ACCT_MULTI_ORG_ID,V_ACCT_CMS_OWNING_branch,V_ACCT_CMS_HOME_PH_1,V_ACCT_CMS_WORK_PH_1,V_ACCT_CMS_CH_1_MOB_1,V_ACCT_CMS_HOME_PH_2,V_ACCT_CMS_WORK_PH_2,V_ACCT_CMS_CH_1_MOB_2,V_ACCT_CMS_ADDR_POSTAL_CODE, V_ALERT_HWM_FRAUD_SCORE, V_ALERT_QUEUE_ID, V_ALERT_ACCT_NUM, V_ALERT_ALERT_ID,V_ALERT_ALERT_STATUS;
WHILE at_end = 0 DO
SET V_CHECKOUT_CNT=0;
SET V_INSERT_CHECKOUT_FLAG=0;
SELECT count(1) into V_CHECKOUT_CNT from hts_checkout where HTS_ACCT_NUM=V_ALERT_ACCT_NUM and HTS_QUEUE_ID=V_ALERT_QUEUE_ID;
SELECT current timestamp into VAR_CURRENT_TIMESTAMP FROM sysibm.sysdummy1;
IF (V_CHECKOUT_CNT=0) THEN
--- insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_ALERT_QUEUE_ID,V_ALERT_ALERT_STATUS,'H',0,VAR_CURRENT_TIMESTAMP,VAR_CURRENT_TIMESTAMP);
SET V_INSERT_CHECKOUT_FLAG=1;
GOTO RECORD_NEXT;
ELSEIF (V_CHECKOUT_CNT>0) THEN
DECLARE checkout_CUR CURSOR for
select HTS_ACCT_NUM,HTS_QUEUE_ID,HTS_ALERT_STATUS,HTS_REQUEST_COUNTER,HTS_CREATE_TIMESTAMP,HTS_UPDT_TIMESTAMP from fcmcore.hts_checkout where HTS_ACCT_NUM=V_ALERT_ACCT_NUM and HTS_QUEUE_ID=V_ALERT_QUEUE_ID;
open checkout_cur;
FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS, V_chkout_HTS_REQUEST_COUNTER, V_chkout_HTS_CREATE_TIMESTAMP, V_chkout_HTS_UPDT_TIMESTAMP;
WHILE at_end1 = 0 DO
SET V_TIME_DIFFERENCE=TIMESTAMPDIFF(SQL_TSI_MINUTE,V_chkout_HTS_CREATE_TIMESTAMP, V_chkout_HTS_UPDT_TIMESTAMP);
IF (V_chkout_HTS_ALERT_STATUS = 'X' or V_chkout_HTS_ALERT_STATUS = 'V' or V_chkout_HTS_ALERT_STATUS = 'S' or V_chkout_HTS_REQUEST_COUNTER = 6) THEN
--- FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS, V_chkout_HTS_REQUEST_COUNTER, V_chkout_HTS_CREATE_TIMESTAMP, V_chkout_HTS_UPDT_TIMESTAMP;
GOTO INTERNAL_LOOP;
END IF;
IF (V_TIME_DIFFERENCE < 30 ) THEN --- Threshold needs to confirm & set from parameter
GOTO INTERNAL_LOOP;
ELSE
SET V_chkout_HTS_REQUEST_COUNTER=V_chkout_HTS_REQUEST_COUNTER+1;
update hts_checkout set HTS_REQUEST_COUNTER=V_chkout_HTS_REQUEST_COUNTER, HTS_UPDT_TIMESTAMP=current timestamp,HTS_ALERT_STATUS = 'X' where HTS_ACCT_NUM=V_chkout_HTS_ACCT_NUM and HTS_QUEUE_ID=V_chkout_HTS_QUEUE_ID;
insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
V_ACCT_CMS_CARD_ORG,
V_ACCT_CMS_CARD_LOGO,
V_ACCT_MULTI_ORG_ID,
V_chkout_HTS_ACCT_NUM,
V_ACCT_CMS_OWNING_branch,
V_chkout_HTS_QUEUE_ID,
0,
V_ALERT_HWM_FRAUD_SCORE,
V_chkout_HTS_REQUEST_COUNTER+1,
V_ACCT_CMS_HOME_PH_1,
V_ACCT_CMS_WORK_PH_1,
V_ACCT_CMS_CH_1_MOB_1,
V_ACCT_CMS_HOME_PH_2,
V_ACCT_CMS_WORK_PH_2,
V_ACCT_CMS_CH_1_MOB_2);
END IF;
IF (V_INSERT_CHECKOUT_FLAG=1) THEN
RECORD_NEXT:
--- Insert dialer out
insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
V_ACCT_CMS_CARD_ORG,
V_ACCT_CMS_CARD_LOGO,
V_ACCT_MULTI_ORG_ID,
V_chkout_HTS_ACCT_NUM,
V_ACCT_CMS_OWNING_branch,
V_chkout_HTS_QUEUE_ID,
0,
V_ALERT_HWM_FRAUD_SCORE,
1,
V_ACCT_CMS_HOME_PH_1,
V_ACCT_CMS_WORK_PH_1,
V_ACCT_CMS_CH_1_MOB_1,
V_ACCT_CMS_HOME_PH_2,
V_ACCT_CMS_WORK_PH_2,
V_ACCT_CMS_CH_1_MOB_2);
--- Insert HTS_CHECKout
insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_ALERT_QUEUE_ID,'X','H',1,VAR_CURRENT_TIMESTAMP,VAR_CURRENT_TIMESTAMP);
ELSE
update hts_checkout set HTS_REQUEST_COUNTER=V_chkout_HTS_REQUEST_COUNTER+1, HTS_UPDT_TIMESTAMP=current timestamp,HTS_ALERT_STATUS = 'X' where HTS_ACCT_NUM=V_chkout_HTS_ACCT_NUM and HTS_QUEUE_ID=V_chkout_HTS_QUEUE_ID;
END IF;
INTERNAL_LOOP:
FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS, V_chkout_HTS_REQUEST_COUNTER, V_chkout_HTS_CREATE_TIMESTAMP, V_chkout_HTS_UPDT_TIMESTAMP;
END WHILE;
CLOSE checkout_cur;
END IF;
FETCH service_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT_CMS_CARD_LOGO,V_ACCT_MULTI_ORG_ID,V_ACCT_CMS_OWNING_branch,V_ACCT_CMS_HOME_PH_1,V_ACCT_CMS_WORK_PH_1,V_ACCT_CMS_CH_1_MOB_1,V_ACCT_CMS_HOME_PH_2,V_ACCT_CMS_WORK_PH_2,V_ACCT_CMS_CH_1_MOB_2,V_ACCT_CMS_ADDR_POSTAL_CODE, V_ALERT_HWM_FRAUD_SCORE, V_ALERT_QUEUE_ID, V_ALERT_ACCT_NUM, V_ALERT_ALERT_ID,V_ALERT_ALERT_STATUS;
END WHILE;
CLOSE service_alert_cur;
COMMIT;
END P1;
@
ASKER
yes Momi_sabag. @ is terminating character.
I do complie it with command - db2 -td@ -f abc.sql
I do complie it with command - db2 -td@ -f abc.sql
have you tried to remove the last semicolon ? that is
SET SCHEMA = FCMCORE@
--DROP PROCEDURE FCMCORE.EXTRACT_VRU@
CREATE PROCEDURE FCMCORE.EXTRACT_VRU ( IN TS TIMESTAMP )
SPECIFIC EXTRACT_VRU
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
P1: BEGIN ATOMIC
--- Start variables for fields used in cursor sercice_alert_cur
DECLARE V_ACCT_CMS_CARD_ORG CHARACTER(3) DEFAULT NULL;
DECLARE V_ACCT_CMS_CARD_LOGO CHARACTER(3) DEFAULT NULL;
DECLARE V_ACCT_MULTI_ORG_ID INT;
DECLARE V_ACCT_CMS_OWNING_branch CHARACTER(9) DEFAULT NULL;
DECLARE V_ACCT_CMS_HOME_PH_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_WORK_PH_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_CH_1_MOB_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_HOME_PH_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_WORK_PH_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_CH_1_MOB_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_ADDR_POSTAL_COD E CHARACTER(9) DEFAULT NULL;
DECLARE V_ALERT_HWM_FRAUD_SCORE INT;
DECLARE V_ALERT_QUEUE_ID INT;
DECLARE V_ALERT_ACCT_NUM CHARACTER(20) DEFAULT NULL;
DECLARE V_ALERT_ALERT_ID INT;
DECLARE V_ALERT_ALERT_STATUS CHARACTER(10) DEFAULT NULL;
--- End variables for fields used in cursor sercice_alert_cur
--- Start variables for fields used in cursor checkout_cur
DECLARE V_chkout_HTS_ACCT_NUM CHARACTER(20);
DECLARE V_chkout_HTS_QUEUE_ID INT DEFAULT NULL;
DECLARE V_chkout_HTS_ALERT_STATUS CHARACTER(1);
DECLARE V_chkout_HTS_REQUEST_COUNT ER INT DEFAULT NULL;
DECLARE V_chkout_HTS_CREATE_TIMEST AMP TIMESTAMP DEFAULT NULL;
DECLARE V_chkout_HTS_UPDT_TIMESTAM P TIMESTAMP DEFAULT NULL;
DECLARE V_TIME_DIFFERENCE INT;
--- End variables for fields used in cursor checkout_cur
DECLARE V_CHECKOUT_CNT INT;
DECLARE VAR_CURRENT_TIMESTAMP TIMESTAMP DEFAULT NULL;
DECLARE V_INSERT_CHECKOUT_FLAG int DEFAULT 0;
DECLARE at_end int default 0;
DECLARE at_end1 int default 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE dup_rec CONDITION FOR SQLSTATE '23505';
DECLARE dup_flag int default 0;
DECLARE not_found1 CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SET at_end=1;
DECLARE CONTINUE HANDLER FOR dup_rec
SET dup_flag=1;
delete from fcmcore.hts_dialer_out;
DECLARE sercice_alert_cur CURSOR for
Select b.cms_card_org,b.cms_card_ logo,b.mul ti_org_id, b.cms_owni ng_branch,
b.CMS_HOME_PHONE_1,b.CMS_W ORK_PHONE_ 1,b.CMS_CH _1_MOBILE_ 1,
b.CMS_HOME_PHONE_2,b.CMS_W ORK_PHONE_ 2,b.CMS_CH _1_MOBILE_ 2,
b.CMS_ADDR_POSTAL_CODE,a.h wm_fraud_s core,a.que ue_id,a.ac count_num,
a.alert_id,a.alert_status from fcm_alert a,fcm_account b
Where
-- a.QUEUE_ID= Business will provide the queue ids and
a.Result_id is null and
a.ALERT_STATUS='ACTIVE' and
a.ACCOUNT_NUM=b.CMS_ACCT_N UM;
-- Opening service alert cusror
open service_alert_cur;
fetch service_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT _CMS_CARD_ LOGO,V_ACC T_MULTI_OR G_ID,V_ACC T_CMS_OWNI NG_branch, V_ACCT_CMS _HOME_PH_1 ,V_ACCT_CM S_WORK_PH_ 1,V_ACCT_C MS_CH_1_MO B_1,V_ACCT _CMS_HOME_ PH_2,V_ACC T_CMS_WORK _PH_2,V_AC CT_CMS_CH_ 1_MOB_2,V_ ACCT_CMS_A DDR_POSTAL _CODE, V_ALERT_HWM_FRAUD_SCORE, V_ALERT_QUEUE_ID, V_ALERT_ACCT_NUM, V_ALERT_ALERT_ID,V_ALERT_A LERT_STATU S;
WHILE at_end = 0 DO
SET V_CHECKOUT_CNT=0;
SET V_INSERT_CHECKOUT_FLAG=0;
SELECT count(1) into V_CHECKOUT_CNT from hts_checkout where HTS_ACCT_NUM=V_ALERT_ACCT_ NUM and HTS_QUEUE_ID=V_ALERT_QUEUE _ID;
SELECT current timestamp into VAR_CURRENT_TIMESTAMP FROM sysibm.sysdummy1;
IF (V_CHECKOUT_CNT=0) THEN
--- insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_ ALERT_QUEU E_ID,V_ALE RT_ALERT_S TATUS,'H', 0,VAR_CURR ENT_TIMEST AMP,VAR_CU RRENT_TIME STAMP);
SET V_INSERT_CHECKOUT_FLAG=1;
GOTO RECORD_NEXT;
ELSEIF (V_CHECKOUT_CNT>0) THEN
DECLARE checkout_CUR CURSOR for
select HTS_ACCT_NUM,HTS_QUEUE_ID, HTS_ALERT_ STATUS,HTS _REQUEST_C OUNTER,HTS _CREATE_TI MESTAMP,HT S_UPDT_TIM ESTAMP from fcmcore.hts_checkout where HTS_ACCT_NUM=V_ALERT_ACCT_ NUM and HTS_QUEUE_ID=V_ALERT_QUEUE _ID;
open checkout_cur;
FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS, V_chkout_HTS_REQUEST_COUNT ER, V_chkout_HTS_CREATE_TIMEST AMP, V_chkout_HTS_UPDT_TIMESTAM P;
WHILE at_end1 = 0 DO
SET V_TIME_DIFFERENCE=TIMESTAM PDIFF(SQL_ TSI_MINUTE ,V_chkout_ HTS_CREATE _TIMESTAMP , V_chkout_HTS_UPDT_TIMESTAM P);
IF (V_chkout_HTS_ALERT_STATUS = 'X' or V_chkout_HTS_ALERT_STATUS = 'V' or V_chkout_HTS_ALERT_STATUS = 'S' or V_chkout_HTS_REQUEST_COUNT ER = 6) THEN
--- FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS, V_chkout_HTS_REQUEST_COUNT ER, V_chkout_HTS_CREATE_TIMEST AMP, V_chkout_HTS_UPDT_TIMESTAM P;
GOTO INTERNAL_LOOP;
END IF;
IF (V_TIME_DIFFERENCE < 30 ) THEN --- Threshold needs to confirm & set from parameter
GOTO INTERNAL_LOOP;
ELSE
SET V_chkout_HTS_REQUEST_COUNT ER=V_chkou t_HTS_REQU EST_COUNTE R+1;
update hts_checkout set HTS_REQUEST_COUNTER=V_chko ut_HTS_REQ UEST_COUNT ER, HTS_UPDT_TIMESTAMP=current timestamp,HTS_ALERT_STATUS = 'X' where HTS_ACCT_NUM=V_chkout_HTS_ ACCT_NUM and HTS_QUEUE_ID=V_chkout_HTS_ QUEUE_ID;
insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
V_ACCT_CMS_CARD_ORG,
V_ACCT_CMS_CARD_LOGO,
V_ACCT_MULTI_ORG_ID,
V_chkout_HTS_ACCT_NUM,
V_ACCT_CMS_OWNING_branch,
V_chkout_HTS_QUEUE_ID,
0,
V_ALERT_HWM_FRAUD_SCORE,
V_chkout_HTS_REQUEST_COUNT ER+1,
V_ACCT_CMS_HOME_PH_1,
V_ACCT_CMS_WORK_PH_1,
V_ACCT_CMS_CH_1_MOB_1,
V_ACCT_CMS_HOME_PH_2,
V_ACCT_CMS_WORK_PH_2,
V_ACCT_CMS_CH_1_MOB_2);
END IF;
IF (V_INSERT_CHECKOUT_FLAG=1) THEN
RECORD_NEXT:
--- Insert dialer out
insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
V_ACCT_CMS_CARD_ORG,
V_ACCT_CMS_CARD_LOGO,
V_ACCT_MULTI_ORG_ID,
V_chkout_HTS_ACCT_NUM,
V_ACCT_CMS_OWNING_branch,
V_chkout_HTS_QUEUE_ID,
0,
V_ALERT_HWM_FRAUD_SCORE,
1,
V_ACCT_CMS_HOME_PH_1,
V_ACCT_CMS_WORK_PH_1,
V_ACCT_CMS_CH_1_MOB_1,
V_ACCT_CMS_HOME_PH_2,
V_ACCT_CMS_WORK_PH_2,
V_ACCT_CMS_CH_1_MOB_2);
--- Insert HTS_CHECKout
insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_ ALERT_QUEU E_ID,'X',' H',1,VAR_C URRENT_TIM ESTAMP,VAR _CURRENT_T IMESTAMP);
ELSE
update hts_checkout set HTS_REQUEST_COUNTER=V_chko ut_HTS_REQ UEST_COUNT ER+1, HTS_UPDT_TIMESTAMP=current timestamp,HTS_ALERT_STATUS = 'X' where HTS_ACCT_NUM=V_chkout_HTS_ ACCT_NUM and HTS_QUEUE_ID=V_chkout_HTS_ QUEUE_ID;
END IF;
INTERNAL_LOOP:
FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS, V_chkout_HTS_REQUEST_COUNT ER, V_chkout_HTS_CREATE_TIMEST AMP, V_chkout_HTS_UPDT_TIMESTAM P;
END WHILE;
CLOSE checkout_cur;
END IF;
FETCH service_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT _CMS_CARD_ LOGO,V_ACC T_MULTI_OR G_ID,V_ACC T_CMS_OWNI NG_branch, V_ACCT_CMS _HOME_PH_1 ,V_ACCT_CM S_WORK_PH_ 1,V_ACCT_C MS_CH_1_MO B_1,V_ACCT _CMS_HOME_ PH_2,V_ACC T_CMS_WORK _PH_2,V_AC CT_CMS_CH_ 1_MOB_2,V_ ACCT_CMS_A DDR_POSTAL _CODE, V_ALERT_HWM_FRAUD_SCORE, V_ALERT_QUEUE_ID, V_ALERT_ACCT_NUM, V_ALERT_ALERT_ID,V_ALERT_A LERT_STATU S;
END WHILE;
CLOSE service_alert_cur;
COMMIT;
END P1
@
SET SCHEMA = FCMCORE@
--DROP PROCEDURE FCMCORE.EXTRACT_VRU@
CREATE PROCEDURE FCMCORE.EXTRACT_VRU ( IN TS TIMESTAMP )
SPECIFIC EXTRACT_VRU
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
P1: BEGIN ATOMIC
--- Start variables for fields used in cursor sercice_alert_cur
DECLARE V_ACCT_CMS_CARD_ORG CHARACTER(3) DEFAULT NULL;
DECLARE V_ACCT_CMS_CARD_LOGO CHARACTER(3) DEFAULT NULL;
DECLARE V_ACCT_MULTI_ORG_ID INT;
DECLARE V_ACCT_CMS_OWNING_branch CHARACTER(9) DEFAULT NULL;
DECLARE V_ACCT_CMS_HOME_PH_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_WORK_PH_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_CH_1_MOB_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_HOME_PH_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_WORK_PH_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_CH_1_MOB_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_ADDR_POSTAL_COD
DECLARE V_ALERT_HWM_FRAUD_SCORE INT;
DECLARE V_ALERT_QUEUE_ID INT;
DECLARE V_ALERT_ACCT_NUM CHARACTER(20) DEFAULT NULL;
DECLARE V_ALERT_ALERT_ID INT;
DECLARE V_ALERT_ALERT_STATUS CHARACTER(10) DEFAULT NULL;
--- End variables for fields used in cursor sercice_alert_cur
--- Start variables for fields used in cursor checkout_cur
DECLARE V_chkout_HTS_ACCT_NUM CHARACTER(20);
DECLARE V_chkout_HTS_QUEUE_ID INT DEFAULT NULL;
DECLARE V_chkout_HTS_ALERT_STATUS CHARACTER(1);
DECLARE V_chkout_HTS_REQUEST_COUNT
DECLARE V_chkout_HTS_CREATE_TIMEST
DECLARE V_chkout_HTS_UPDT_TIMESTAM
DECLARE V_TIME_DIFFERENCE INT;
--- End variables for fields used in cursor checkout_cur
DECLARE V_CHECKOUT_CNT INT;
DECLARE VAR_CURRENT_TIMESTAMP TIMESTAMP DEFAULT NULL;
DECLARE V_INSERT_CHECKOUT_FLAG int DEFAULT 0;
DECLARE at_end int default 0;
DECLARE at_end1 int default 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE dup_rec CONDITION FOR SQLSTATE '23505';
DECLARE dup_flag int default 0;
DECLARE not_found1 CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SET at_end=1;
DECLARE CONTINUE HANDLER FOR dup_rec
SET dup_flag=1;
delete from fcmcore.hts_dialer_out;
DECLARE sercice_alert_cur CURSOR for
Select b.cms_card_org,b.cms_card_
b.CMS_HOME_PHONE_1,b.CMS_W
b.CMS_HOME_PHONE_2,b.CMS_W
b.CMS_ADDR_POSTAL_CODE,a.h
a.alert_id,a.alert_status from fcm_alert a,fcm_account b
Where
-- a.QUEUE_ID= Business will provide the queue ids and
a.Result_id is null and
a.ALERT_STATUS='ACTIVE' and
a.ACCOUNT_NUM=b.CMS_ACCT_N
-- Opening service alert cusror
open service_alert_cur;
fetch service_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT
WHILE at_end = 0 DO
SET V_CHECKOUT_CNT=0;
SET V_INSERT_CHECKOUT_FLAG=0;
SELECT count(1) into V_CHECKOUT_CNT from hts_checkout where HTS_ACCT_NUM=V_ALERT_ACCT_
SELECT current timestamp into VAR_CURRENT_TIMESTAMP FROM sysibm.sysdummy1;
IF (V_CHECKOUT_CNT=0) THEN
--- insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_
SET V_INSERT_CHECKOUT_FLAG=1;
GOTO RECORD_NEXT;
ELSEIF (V_CHECKOUT_CNT>0) THEN
DECLARE checkout_CUR CURSOR for
select HTS_ACCT_NUM,HTS_QUEUE_ID,
open checkout_cur;
FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS,
WHILE at_end1 = 0 DO
SET V_TIME_DIFFERENCE=TIMESTAM
IF (V_chkout_HTS_ALERT_STATUS
--- FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS,
GOTO INTERNAL_LOOP;
END IF;
IF (V_TIME_DIFFERENCE < 30 ) THEN --- Threshold needs to confirm & set from parameter
GOTO INTERNAL_LOOP;
ELSE
SET V_chkout_HTS_REQUEST_COUNT
update hts_checkout set HTS_REQUEST_COUNTER=V_chko
insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
V_ACCT_CMS_CARD_ORG,
V_ACCT_CMS_CARD_LOGO,
V_ACCT_MULTI_ORG_ID,
V_chkout_HTS_ACCT_NUM,
V_ACCT_CMS_OWNING_branch,
V_chkout_HTS_QUEUE_ID,
0,
V_ALERT_HWM_FRAUD_SCORE,
V_chkout_HTS_REQUEST_COUNT
V_ACCT_CMS_HOME_PH_1,
V_ACCT_CMS_WORK_PH_1,
V_ACCT_CMS_CH_1_MOB_1,
V_ACCT_CMS_HOME_PH_2,
V_ACCT_CMS_WORK_PH_2,
V_ACCT_CMS_CH_1_MOB_2);
END IF;
IF (V_INSERT_CHECKOUT_FLAG=1)
RECORD_NEXT:
--- Insert dialer out
insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
V_ACCT_CMS_CARD_ORG,
V_ACCT_CMS_CARD_LOGO,
V_ACCT_MULTI_ORG_ID,
V_chkout_HTS_ACCT_NUM,
V_ACCT_CMS_OWNING_branch,
V_chkout_HTS_QUEUE_ID,
0,
V_ALERT_HWM_FRAUD_SCORE,
1,
V_ACCT_CMS_HOME_PH_1,
V_ACCT_CMS_WORK_PH_1,
V_ACCT_CMS_CH_1_MOB_1,
V_ACCT_CMS_HOME_PH_2,
V_ACCT_CMS_WORK_PH_2,
V_ACCT_CMS_CH_1_MOB_2);
--- Insert HTS_CHECKout
insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_
ELSE
update hts_checkout set HTS_REQUEST_COUNTER=V_chko
END IF;
INTERNAL_LOOP:
FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS,
END WHILE;
CLOSE checkout_cur;
END IF;
FETCH service_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT
END WHILE;
CLOSE service_alert_cur;
COMMIT;
END P1
@
ASKER
Hi Momi_sabag,
After removing last semicolon ( as mentioned above ), I am getting below error
DB20000I The SQL command completed successfully.
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "<cursor declaration>" was found following "".
Expected tokens may include: "<SQL statement>". LINE NUMBER=60.
SQLSTATE=42601
Line number 60 is - delete from fcmcore.hts_dialer_out;
After removing last semicolon ( as mentioned above ), I am getting below error
DB20000I The SQL command completed successfully.
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "<cursor declaration>" was found following "".
Expected tokens may include: "<SQL statement>". LINE NUMBER=60.
SQLSTATE=42601
Line number 60 is - delete from fcmcore.hts_dialer_out;
move the delete statement after the declare cursor statement
ASKER
tried same & it is giving below error -
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "<cursor declaration>" was found following "".
Expected tokens may include: "<SQL statement>". LINE NUMBER=59.
SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "<cursor declaration>" was found following "".
Expected tokens may include: "<SQL statement>". LINE NUMBER=59.
SQLSTATE=42601
try either put the cursor declarations before the handlers declaration or open another code block using begin before the cursor declartion
ASKER
I tried to put the cursor declarations before the handlers declaration & received -
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token ";" was found following "_cur; COMMIT; END P1".
Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=163.
SQLSTATE=42601
========================== ========== ========== ========== ==========
I tried to keep different block before cursor declaration & received below error -
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END" was found following "SET dup_flag=1; ".
Expected tokens may include: "<psm_begin_compound_stmt> ". LINE NUMBER=58.
SQLSTATE=42601
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token ";" was found following "_cur; COMMIT; END P1".
Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=163.
SQLSTATE=42601
==========================
I tried to keep different block before cursor declaration & received below error -
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END" was found following "SET dup_flag=1; ".
Expected tokens may include: "<psm_begin_compound_stmt>
SQLSTATE=42601
SET SCHEMA = FCMCORE@
--DROP PROCEDURE FCMCORE.EXTRACT_VRU@
CREATE PROCEDURE FCMCORE.EXTRACT_VRU ( IN TS TIMESTAMP )
SPECIFIC EXTRACT_VRU
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
P1: BEGIN ATOMIC
--- Start variables for fields used in cursor sercice_alert_cur
DECLARE V_ACCT_CMS_CARD_ORG CHARACTER(3) DEFAULT NULL;
DECLARE V_ACCT_CMS_CARD_LOGO CHARACTER(3) DEFAULT NULL;
DECLARE V_ACCT_MULTI_ORG_ID INT;
DECLARE V_ACCT_CMS_OWNING_branch CHARACTER(9) DEFAULT NULL;
DECLARE V_ACCT_CMS_HOME_PH_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_WORK_PH_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_CH_1_MOB_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_HOME_PH_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_WORK_PH_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_CH_1_MOB_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_ADDR_POSTAL_COD E CHARACTER(9) DEFAULT NULL;
DECLARE V_ALERT_HWM_FRAUD_SCORE INT;
DECLARE V_ALERT_QUEUE_ID INT;
DECLARE V_ALERT_ACCT_NUM CHARACTER(20) DEFAULT NULL;
DECLARE V_ALERT_ALERT_ID INT;
DECLARE V_ALERT_ALERT_STATUS CHARACTER(10) DEFAULT NULL;
--- End variables for fields used in cursor sercice_alert_cur
--- Start variables for fields used in cursor checkout_cur
DECLARE V_chkout_HTS_ACCT_NUM CHARACTER(20);
DECLARE V_chkout_HTS_QUEUE_ID INT DEFAULT NULL;
DECLARE V_chkout_HTS_ALERT_STATUS CHARACTER(1);
DECLARE V_chkout_HTS_REQUEST_COUNT ER INT DEFAULT NULL;
DECLARE V_chkout_HTS_CREATE_TIMEST AMP TIMESTAMP DEFAULT NULL;
DECLARE V_chkout_HTS_UPDT_TIMESTAM P TIMESTAMP DEFAULT NULL;
DECLARE V_TIME_DIFFERENCE INT;
--- End variables for fields used in cursor checkout_cur
DECLARE V_CHECKOUT_CNT INT;
DECLARE VAR_CURRENT_TIMESTAMP TIMESTAMP DEFAULT NULL;
DECLARE V_INSERT_CHECKOUT_FLAG int DEFAULT 0;
DECLARE at_end int default 0;
DECLARE at_end1 int default 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE dup_rec CONDITION FOR SQLSTATE '23505';
DECLARE dup_flag int default 0;
DECLARE not_found1 CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SET at_end=1;
DECLARE CONTINUE HANDLER FOR dup_rec
SET dup_flag=1;
BEGIN
DECLARE sercice_alert_cur CURSOR for
Select b.cms_card_org,b.cms_card_ logo,b.mul ti_org_id, b.cms_owni ng_branch,
b.CMS_HOME_PHONE_1,b.CMS_W ORK_PHONE_ 1,b.CMS_CH _1_MOBILE_ 1,
b.CMS_HOME_PHONE_2,b.CMS_W ORK_PHONE_ 2,b.CMS_CH _1_MOBILE_ 2,
b.CMS_ADDR_POSTAL_CODE,a.h wm_fraud_s core,a.que ue_id,a.ac count_num,
a.alert_id,a.alert_status from fcm_alert a,fcm_account b
Where
-- a.QUEUE_ID= Business will provide the queue ids and
a.Result_id is null and
a.ALERT_STATUS='ACTIVE' and
a.ACCOUNT_NUM=b.CMS_ACCT_N UM;
delete from fcmcore.hts_dialer_out;
-- Opening service alert cusror
open service_alert_cur;
fetch service_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT _CMS_CARD_ LOGO,V_ACC T_MULTI_OR G_ID,V_ACC T_CMS_OWNI NG_branch, V_ACCT_CMS _HOME_PH_1 ,V_ACCT_CM S_WORK_PH_ 1,V_ACCT_C MS_CH_1_MO B_1,V_ACCT _CMS_HOME_ PH_2,V_ACC T_CMS_WORK _PH_2,V_AC CT_CMS_CH_ 1_MOB_2,V_ ACCT_CMS_A DDR_POSTAL _CODE, V_ALERT_HWM_FRAUD_SCORE, V_ALERT_QUEUE_ID, V_ALERT_ACCT_NUM, V_ALERT_ALERT_ID,V_ALERT_A LERT_STATU S;
WHILE at_end = 0 DO
SET V_CHECKOUT_CNT=0;
SET V_INSERT_CHECKOUT_FLAG=0;
SELECT count(1) into V_CHECKOUT_CNT from hts_checkout where HTS_ACCT_NUM=V_ALERT_ACCT_ NUM and HTS_QUEUE_ID=V_ALERT_QUEUE _ID;
SELECT current timestamp into VAR_CURRENT_TIMESTAMP FROM sysibm.sysdummy1;
IF (V_CHECKOUT_CNT=0) THEN
--- insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_ ALERT_QUEU E_ID,V_ALE RT_ALERT_S TATUS,'H', 0,VAR_CURR ENT_TIMEST AMP,VAR_CU RRENT_TIME STAMP);
SET V_INSERT_CHECKOUT_FLAG=1;
GOTO RECORD_NEXT;
ELSEIF (V_CHECKOUT_CNT>0) THEN
DECLARE checkout_CUR CURSOR for
select HTS_ACCT_NUM,HTS_QUEUE_ID, HTS_ALERT_ STATUS,HTS _REQUEST_C OUNTER,HTS _CREATE_TI MESTAMP,HT S_UPDT_TIM ESTAMP from fcmcore.hts_checkout where HTS_ACCT_NUM=V_ALERT_ACCT_ NUM and HTS_QUEUE_ID=V_ALERT_QUEUE _ID;
open checkout_cur;
FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS, V_chkout_HTS_REQUEST_COUNT ER, V_chkout_HTS_CREATE_TIMEST AMP, V_chkout_HTS_UPDT_TIMESTAM P;
WHILE at_end1 = 0 DO
SET V_TIME_DIFFERENCE=TIMESTAM PDIFF(SQL_ TSI_MINUTE ,V_chkout_ HTS_CREATE _TIMESTAMP , V_chkout_HTS_UPDT_TIMESTAM P);
IF (V_chkout_HTS_ALERT_STATUS = 'X' or V_chkout_HTS_ALERT_STATUS = 'V' or V_chkout_HTS_ALERT_STATUS = 'S' or V_chkout_HTS_REQUEST_COUNT ER = 6) THEN
--- FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS, V_chkout_HTS_REQUEST_COUNT ER, V_chkout_HTS_CREATE_TIMEST AMP, V_chkout_HTS_UPDT_TIMESTAM P;
GOTO INTERNAL_LOOP;
END IF;
IF (V_TIME_DIFFERENCE < 30 ) THEN --- Threshold needs to confirm & set from parameter
GOTO INTERNAL_LOOP;
ELSE
SET V_chkout_HTS_REQUEST_COUNT ER=V_chkou t_HTS_REQU EST_COUNTE R+1;
update hts_checkout set HTS_REQUEST_COUNTER=V_chko ut_HTS_REQ UEST_COUNT ER, HTS_UPDT_TIMESTAMP=current timestamp,HTS_ALERT_STATUS = 'X' where HTS_ACCT_NUM=V_chkout_HTS_ ACCT_NUM and HTS_QUEUE_ID=V_chkout_HTS_ QUEUE_ID;
insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
V_ACCT_CMS_CARD_ORG,
V_ACCT_CMS_CARD_LOGO,
V_ACCT_MULTI_ORG_ID,
V_chkout_HTS_ACCT_NUM,
V_ACCT_CMS_OWNING_branch,
V_chkout_HTS_QUEUE_ID,
0,
V_ALERT_HWM_FRAUD_SCORE,
V_chkout_HTS_REQUEST_COUNT ER+1,
V_ACCT_CMS_HOME_PH_1,
V_ACCT_CMS_WORK_PH_1,
V_ACCT_CMS_CH_1_MOB_1,
V_ACCT_CMS_HOME_PH_2,
V_ACCT_CMS_WORK_PH_2,
V_ACCT_CMS_CH_1_MOB_2);
END IF;
IF (V_INSERT_CHECKOUT_FLAG=1) THEN
RECORD_NEXT:
--- Insert dialer out
insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
V_ACCT_CMS_CARD_ORG,
V_ACCT_CMS_CARD_LOGO,
V_ACCT_MULTI_ORG_ID,
V_chkout_HTS_ACCT_NUM,
V_ACCT_CMS_OWNING_branch,
V_chkout_HTS_QUEUE_ID,
0,
V_ALERT_HWM_FRAUD_SCORE,
1,
V_ACCT_CMS_HOME_PH_1,
V_ACCT_CMS_WORK_PH_1,
V_ACCT_CMS_CH_1_MOB_1,
V_ACCT_CMS_HOME_PH_2,
V_ACCT_CMS_WORK_PH_2,
V_ACCT_CMS_CH_1_MOB_2);
--- Insert HTS_CHECKout
insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_ ALERT_QUEU E_ID,'X',' H',1,VAR_C URRENT_TIM ESTAMP,VAR _CURRENT_T IMESTAMP);
ELSE
update hts_checkout set HTS_REQUEST_COUNTER=V_chko ut_HTS_REQ UEST_COUNT ER+1, HTS_UPDT_TIMESTAMP=current timestamp,HTS_ALERT_STATUS = 'X' where HTS_ACCT_NUM=V_chkout_HTS_ ACCT_NUM and HTS_QUEUE_ID=V_chkout_HTS_ QUEUE_ID;
END IF;
INTERNAL_LOOP:
FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS, V_chkout_HTS_REQUEST_COUNT ER, V_chkout_HTS_CREATE_TIMEST AMP, V_chkout_HTS_UPDT_TIMESTAM P;
END WHILE;
CLOSE checkout_cur;
END IF;
FETCH service_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT _CMS_CARD_ LOGO,V_ACC T_MULTI_OR G_ID,V_ACC T_CMS_OWNI NG_branch, V_ACCT_CMS _HOME_PH_1 ,V_ACCT_CM S_WORK_PH_ 1,V_ACCT_C MS_CH_1_MO B_1,V_ACCT _CMS_HOME_ PH_2,V_ACC T_CMS_WORK _PH_2,V_AC CT_CMS_CH_ 1_MOB_2,V_ ACCT_CMS_A DDR_POSTAL _CODE, V_ALERT_HWM_FRAUD_SCORE, V_ALERT_QUEUE_ID, V_ALERT_ACCT_NUM, V_ALERT_ALERT_ID,V_ALERT_A LERT_STATU S;
END WHILE;
CLOSE service_alert_cur;
COMMIT;
END;
END P1;
@
--DROP PROCEDURE FCMCORE.EXTRACT_VRU@
CREATE PROCEDURE FCMCORE.EXTRACT_VRU ( IN TS TIMESTAMP )
SPECIFIC EXTRACT_VRU
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
P1: BEGIN ATOMIC
--- Start variables for fields used in cursor sercice_alert_cur
DECLARE V_ACCT_CMS_CARD_ORG CHARACTER(3) DEFAULT NULL;
DECLARE V_ACCT_CMS_CARD_LOGO CHARACTER(3) DEFAULT NULL;
DECLARE V_ACCT_MULTI_ORG_ID INT;
DECLARE V_ACCT_CMS_OWNING_branch CHARACTER(9) DEFAULT NULL;
DECLARE V_ACCT_CMS_HOME_PH_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_WORK_PH_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_CH_1_MOB_1 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_HOME_PH_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_WORK_PH_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_CH_1_MOB_2 CHARACTER(15) DEFAULT NULL;
DECLARE V_ACCT_CMS_ADDR_POSTAL_COD
DECLARE V_ALERT_HWM_FRAUD_SCORE INT;
DECLARE V_ALERT_QUEUE_ID INT;
DECLARE V_ALERT_ACCT_NUM CHARACTER(20) DEFAULT NULL;
DECLARE V_ALERT_ALERT_ID INT;
DECLARE V_ALERT_ALERT_STATUS CHARACTER(10) DEFAULT NULL;
--- End variables for fields used in cursor sercice_alert_cur
--- Start variables for fields used in cursor checkout_cur
DECLARE V_chkout_HTS_ACCT_NUM CHARACTER(20);
DECLARE V_chkout_HTS_QUEUE_ID INT DEFAULT NULL;
DECLARE V_chkout_HTS_ALERT_STATUS CHARACTER(1);
DECLARE V_chkout_HTS_REQUEST_COUNT
DECLARE V_chkout_HTS_CREATE_TIMEST
DECLARE V_chkout_HTS_UPDT_TIMESTAM
DECLARE V_TIME_DIFFERENCE INT;
--- End variables for fields used in cursor checkout_cur
DECLARE V_CHECKOUT_CNT INT;
DECLARE VAR_CURRENT_TIMESTAMP TIMESTAMP DEFAULT NULL;
DECLARE V_INSERT_CHECKOUT_FLAG int DEFAULT 0;
DECLARE at_end int default 0;
DECLARE at_end1 int default 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE dup_rec CONDITION FOR SQLSTATE '23505';
DECLARE dup_flag int default 0;
DECLARE not_found1 CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SET at_end=1;
DECLARE CONTINUE HANDLER FOR dup_rec
SET dup_flag=1;
BEGIN
DECLARE sercice_alert_cur CURSOR for
Select b.cms_card_org,b.cms_card_
b.CMS_HOME_PHONE_1,b.CMS_W
b.CMS_HOME_PHONE_2,b.CMS_W
b.CMS_ADDR_POSTAL_CODE,a.h
a.alert_id,a.alert_status from fcm_alert a,fcm_account b
Where
-- a.QUEUE_ID= Business will provide the queue ids and
a.Result_id is null and
a.ALERT_STATUS='ACTIVE' and
a.ACCOUNT_NUM=b.CMS_ACCT_N
delete from fcmcore.hts_dialer_out;
-- Opening service alert cusror
open service_alert_cur;
fetch service_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT
WHILE at_end = 0 DO
SET V_CHECKOUT_CNT=0;
SET V_INSERT_CHECKOUT_FLAG=0;
SELECT count(1) into V_CHECKOUT_CNT from hts_checkout where HTS_ACCT_NUM=V_ALERT_ACCT_
SELECT current timestamp into VAR_CURRENT_TIMESTAMP FROM sysibm.sysdummy1;
IF (V_CHECKOUT_CNT=0) THEN
--- insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_
SET V_INSERT_CHECKOUT_FLAG=1;
GOTO RECORD_NEXT;
ELSEIF (V_CHECKOUT_CNT>0) THEN
DECLARE checkout_CUR CURSOR for
select HTS_ACCT_NUM,HTS_QUEUE_ID,
open checkout_cur;
FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS,
WHILE at_end1 = 0 DO
SET V_TIME_DIFFERENCE=TIMESTAM
IF (V_chkout_HTS_ALERT_STATUS
--- FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS,
GOTO INTERNAL_LOOP;
END IF;
IF (V_TIME_DIFFERENCE < 30 ) THEN --- Threshold needs to confirm & set from parameter
GOTO INTERNAL_LOOP;
ELSE
SET V_chkout_HTS_REQUEST_COUNT
update hts_checkout set HTS_REQUEST_COUNTER=V_chko
insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
V_ACCT_CMS_CARD_ORG,
V_ACCT_CMS_CARD_LOGO,
V_ACCT_MULTI_ORG_ID,
V_chkout_HTS_ACCT_NUM,
V_ACCT_CMS_OWNING_branch,
V_chkout_HTS_QUEUE_ID,
0,
V_ALERT_HWM_FRAUD_SCORE,
V_chkout_HTS_REQUEST_COUNT
V_ACCT_CMS_HOME_PH_1,
V_ACCT_CMS_WORK_PH_1,
V_ACCT_CMS_CH_1_MOB_1,
V_ACCT_CMS_HOME_PH_2,
V_ACCT_CMS_WORK_PH_2,
V_ACCT_CMS_CH_1_MOB_2);
END IF;
IF (V_INSERT_CHECKOUT_FLAG=1)
RECORD_NEXT:
--- Insert dialer out
insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
V_ACCT_CMS_CARD_ORG,
V_ACCT_CMS_CARD_LOGO,
V_ACCT_MULTI_ORG_ID,
V_chkout_HTS_ACCT_NUM,
V_ACCT_CMS_OWNING_branch,
V_chkout_HTS_QUEUE_ID,
0,
V_ALERT_HWM_FRAUD_SCORE,
1,
V_ACCT_CMS_HOME_PH_1,
V_ACCT_CMS_WORK_PH_1,
V_ACCT_CMS_CH_1_MOB_1,
V_ACCT_CMS_HOME_PH_2,
V_ACCT_CMS_WORK_PH_2,
V_ACCT_CMS_CH_1_MOB_2);
--- Insert HTS_CHECKout
insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_
ELSE
update hts_checkout set HTS_REQUEST_COUNTER=V_chko
END IF;
INTERNAL_LOOP:
FETCH checkout_cur into V_chkout_HTS_ACCT_NUM, V_chkout_HTS_QUEUE_ID, V_chkout_HTS_ALERT_STATUS,
END WHILE;
CLOSE checkout_cur;
END IF;
FETCH service_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT
END WHILE;
CLOSE service_alert_cur;
COMMIT;
END;
END P1;
@
ASKER
Tried above code & below error received -
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token ";" was found following " COMMIT; END; END P1".
Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=162.
SQLSTATE=42601
----seems tough time for me :)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token ";" was found following " COMMIT; END; END P1".
Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=162.
SQLSTATE=42601
----seems tough time for me :)
Hi Amit,
That last SQL reintroduced the original error. Remove the semi-colon after 'END P1'.
Kent
That last SQL reintroduced the original error. Remove the semi-colon after 'END P1'.
Kent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
are you sure you've picked @ to be the statement terminator character?