We help IT Professionals succeed at work.

getting error - SQLSTATE=42601 in UDB

Amit_Pekamwar
on
3,165 Views
Last Modified: 2012-05-06
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


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;
@

Open in new window

Comment
Watch Question

how do you run this create statement?
are you sure you've picked @ to be the statement terminator character?

Author

Commented:
yes Momi_sabag. @ is terminating character.
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_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
@

Author

Commented:
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;
move the delete statement after the declare cursor statement

Author

Commented:
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
try either put the cursor declarations before the handlers declaration or open another code block using begin before the cursor declartion

Author

Commented:
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
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;
 

 BEGIN
        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;
 

      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_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;
END P1;
@

Author

Commented:
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 :)
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Amit,

That last SQL reintroduced the original error.  Remove the semi-colon after 'END P1'.


Kent
Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.