Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

getting error - SQLSTATE=42601 in UDB

Posted on 2009-05-06
12
Medium Priority
?
2,592 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

0
Comment
Question by:Amit_Pekamwar
  • 5
  • 5
  • 2
12 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24313553
how do you run this create statement?
are you sure you've picked @ to be the statement terminator character?
0
 

Author Comment

by:Amit_Pekamwar
ID: 24313586
yes Momi_sabag. @ is terminating character.
I do complie it with command - db2 -td@ -f abc.sql
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24313617
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:Amit_Pekamwar
ID: 24313818
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24313988
move the delete statement after the declare cursor statement
0
 

Author Comment

by:Amit_Pekamwar
ID: 24314036
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24314133
try either put the cursor declarations before the handlers declaration or open another code block using begin before the cursor declartion
0
 

Author Comment

by:Amit_Pekamwar
ID: 24314417
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24314495
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
 

Author Comment

by:Amit_Pekamwar
ID: 24314567
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24316182
Hi Amit,

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


Kent
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1500 total points
ID: 24316287
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question