Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2623
  • Last Modified:

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


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

0
Amit_Pekamwar
Asked:
Amit_Pekamwar
  • 5
  • 5
  • 2
1 Solution
 
momi_sabagCommented:
how do you run this create statement?
are you sure you've picked @ to be the statement terminator character?
0
 
Amit_PekamwarAuthor Commented:
yes Momi_sabag. @ is terminating character.
I do complie it with command - db2 -td@ -f abc.sql
0
 
momi_sabagCommented:
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
@
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Amit_PekamwarAuthor 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;
0
 
momi_sabagCommented:
move the delete statement after the declare cursor statement
0
 
Amit_PekamwarAuthor 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
0
 
momi_sabagCommented:
try either put the cursor declarations before the handlers declaration or open another code block using begin before the cursor declartion
0
 
Amit_PekamwarAuthor 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
0
 
momi_sabagCommented:
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;
@
0
 
Amit_PekamwarAuthor 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 :)
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Amit,

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


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Amit,

Lots of bugs to work out.

You've mispelled 'service' in declaring cursor 'service_alert_cur' ('sercice_alert_cur ').
The cursor declaration around line 90 should be move so that it's declared with the other DECLARE statements.
Your cursors probably need a 'WITH HOLD' clause.


Kent
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now