Solved

getting error - SQLSTATE=42601 in UDB

Posted on 2009-05-06
12
2,406 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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 45

Expert Comment

by:Kdo
ID: 24316182
Hi Amit,

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


Kent
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now