Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    10

    Unanswered: DB2 SQL0104N error

    Hi all,

    I am trying to create a procedure in db2 its giving an error,could you kindly help me with this


    DROP PROCEDURE BOUNCEINC_COUNT1
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0204N "DEVTEAM.BOUNCEINC_COUNT1" is an undefined name. SQLSTATE=42704

    CREATE PROCEDURE BOUNCEINC_COUNT1
    (
    IN starting TIMESTAMP ,
    IN ending TIMESTAMP )
    LANGUAGE SQL
    p1: BEGIN

    DECLARE c1_OPEN_FLAG INTEGER DEFAULT 0;

    DECLARE c1_FOUND INTEGER DEFAULT NULL;

    DECLARE SQLCODE INTEGER DEFAULT 0;
    BEGIN

    DECLARE err_value CHAR(1) ;

    DECLARE var_reason1 VARCHAR(26) ;

    DECLARE var_reason2 VARCHAR(26) ;

    DECLARE vBounce_reason VARCHAR(2000) ;
    DECLARE at_end INTeger DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    DECLARE flag_status FLOAT DEFAULT 0;

    DECLARE c1rec_* VARCHAR(19) /****** adventnet-swissql table used in cursor is not found in metadata or metadata not updated ***********/ ;

    DECLARE c1 CURSOR WITH HOLD FOR SELECT *
    FROM bounceback_inc
    WHERE made between starting and ending
    ;
    BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '42710'
    BEGIN
    END;
    DECLARE GLOBAL TEMPORARY TABLE hard_bounce( tempIdx INTEGER, hard_bounce VARCHAR(500)) ON COMMIT PRESERVE ROWS;

    END;

    BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '42710'
    BEGIN
    END;
    DECLARE GLOBAL TEMPORARY TABLE soft_bounce( tempIdx INTEGER, soft_bounce VARCHAR(500)) ON COMMIT PRESERVE ROWS;

    END;

    SET var_reason1 = null;

    SET var_reason2 = null;

    SET vBounce_reason = null;

    SET c1_OPEN_FLAG = 1;


    OPEN c1 ;

    IF EXISTS(SELECT * FROM SESSION.hard_bounce WHERE tempIdx = 1)
    THEN
    UPDATE SESSION.hard_bounce SET hard_bounce = '%invalid local real name%' WHERE tempIdx = 1;
    ELSE
    INSERT INTO SESSION.hard_bounce ( tempIdx , hard_bounce) VALUES(1, '%invalid local real name%' );
    END IF;


    IF EXISTS(SELECT * FROM SESSION.hard_bounce WHERE tempIdx = 2)
    THEN
    UPDATE SESSION.hard_bounce SET hard_bounce = '%no user%' WHERE tempIdx = 2;
    ELSE
    INSERT INTO SESSION.hard_bounce ( tempIdx , hard_bounce) VALUES(2, '%no user%' );
    END IF;






    IF EXISTS(SELECT * FROM SESSION.soft_bounce WHERE tempIdx = 1)
    THEN
    UPDATE SESSION.soft_bounce SET soft_bounce = '%blocked server%' WHERE tempIdx = 1;
    ELSE
    INSERT INTO SESSION.soft_bounce ( tempIdx , soft_bounce) VALUES(1, '%blocked server%' );
    END IF;


    IF EXISTS(SELECT * FROM SESSION.soft_bounce WHERE tempIdx = 2)
    THEN
    UPDATE SESSION.soft_bounce SET soft_bounce = '%mailbox full%' WHERE tempIdx = 2;
    ELSE
    INSERT INTO SESSION.soft_bounce ( tempIdx , soft_bounce) VALUES(2, '%mailbox full%' );
    END IF;





    -- ADVENTNET SWISSQL : ORACLE_SUBSTR IS SwisSQL-OracleToDB2 Library Function. Contact swissql-support@adventnet.com for Implementation
    LOOP_LABEL1: LOOP
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET c1_FOUND = SQLCODE ;
    SET c1_FOUND = 0;
    FETCH FROM c1 INTO c1rec_* ;
    END ;

    IF c1_FOUND = 100 THEN
    LEAVE LOOP_LABEL1;
    END IF;

    BEGIN

    DECLARE j INTEGER ;

    SET j = 1;

    ---- Checking for Hard Bounces---

    LOOP_LABEL2:
    WHILE j <= 13 DO


    IF ( LOWER(c1rec_subject)like (SELECT hard_bounce FROM SESSION.hard_bounce WHERE tempIdx = j ) or LOWER(c1rec_msg)like (SELECT hard_bounce FROM SESSION.hard_bounce WHERE tempIdx = j ) ) THEN

    SET vBounce_reason = VARCHAR(RTRIM(CHAR(REPLACE((SELECT hard_bounce FROM SESSION.hard_bounce WHERE tempIdx = j ), '%' , '' ))));

    SET flag_status = 1;

    LEAVE LOOP_LABEL2;
    ELSEIF ( LOWER(c1rec_subject)like (SELECT soft_bounce FROM SESSION.soft_bounce WHERE tempIdx = j ) or LOWER(c1rec_msg)like (SELECT soft_bounce FROM SESSION.soft_bounce WHERE tempIdx = j ) ) THEN

    SET flag_status = 2;

    SET vBounce_reason = VARCHAR(RTRIM(CHAR(REPLACE((SELECT soft_bounce FROM SESSION.soft_bounce WHERE tempIdx = j ), '%' , '' ))));

    LEAVE LOOP_LABEL2;
    ELSE

    SET flag_status = 0;

    SET vBounce_reason = ORACLE_SUBSTR(VARCHAR(RTRIM(CHAR(c1rec_subject))), 1, 100);

    LEAVE LOOP_LABEL2;
    END IF ;

    SET j = j + 1 ;

    END WHILE ;

    END;
    IF ( flag_status = 1 ) THEN


    BEGIN

    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
    set at_end=0;
    UPDATE bounceback_count
    SET bounceback_count.HardBounce = HardBounce + 1,
    bounceback_count.updat = c1rec_updat,
    bounceback_count.Bounce_reason = vBounce_reason
    WHERE email = c1rec_email
    AND CAMPAIGN = c1rec_CAMPAIGN
    ;
    END;

    IF (at_end=1 ) THEN

    INSERT INTO bounceback_count
    ( email ,
    vemail ,
    campaign ,
    REASON ,
    REASON_COUNT ,
    HardBounce ,
    SoftBounce ,
    Bounce_Reason )
    VALUES ( c1rec_email ,
    LCASE(c1rec_email) ,
    c1rec_campaign ,
    'HardBounce' ,
    1 ,
    1 ,
    0 ,
    vBounce_reason )
    ;
    END IF ;

    SELECT REASON
    INTO VAR_REASON1 FROM BOUNCEBACK_COUNT
    WHERE email = c1rec_email
    AND CAMPAIGN = c1rec_CAMPAIGN
    ;

    IF VAR_REASON1 = 'HardBounce' THEN

    SET var_reason1 = 'HardBounce' ;
    ELSEIF VAR_REASON1 = 'SoftBounce' THEN

    SET var_reason1 = 'HardBounce,SoftBounce' ;
    END IF ;


    BEGIN
    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
    set at_end=0;
    UPDATE bounceback_count
    SET bounceback_count.REASON_COUNT = REASON_COUNT + 1,
    bounceback_count.REASON = var_reason1
    WHERE email = c1rec_email
    AND CAMPAIGN = c1rec_CAMPAIGN
    ;
    END;

    ELSEIF ( flag_status = 2 ) THEN


    BEGIN
    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
    set at_end=0;
    UPDATE bounceback_count
    SET bounceback_count.SoftBounce = SoftBounce + 1,
    bounceback_count.updat = c1rec_updat,
    bounceback_count.bounce_reason = vBounce_reason
    WHERE email = c1rec_email
    AND CAMPAIGN = c1rec_CAMPAIGN
    ;
    END;

    IF (at_end=1) THEN

    INSERT INTO bounceback_count
    ( email ,
    vemail ,
    campaign ,
    reason ,
    reason_count ,
    HardBounce ,
    SoftBounce ,
    Bounce_Reason )
    VALUES ( c1rec_email ,
    LCASE(c1rec_email) ,
    c1rec_campaign ,
    'SoftBounce' ,
    1 ,
    0 ,
    1 ,
    vBounce_reason )
    ;
    END IF ;

    SELECT REASON
    INTO VAR_REASON2 FROM BOUNCEBACK_COUNT
    WHERE email = c1rec_email
    AND CAMPAIGN = c1rec_CAMPAIGN
    ;

    IF VAR_REASON2 = 'HardBounce' THEN

    SET var_reason2 = 'HardBounce,SoftBounce' ;
    ELSEIF VAR_REASON2 = 'SoftBounce' THEN

    SET var_reason2 = 'SoftBounce' ;
    END IF ;


    BEGIN
    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
    set at_end=0;
    UPDATE bounceback_count
    SET bounceback_count.REASON_COUNT = REASON_COUNT + 1,
    bounceback_count.REASON = var_reason2
    WHERE email = c1rec_email
    AND CAMPAIGN = c1rec_CAMPAIGN
    ;
    END;

    ELSEIF ( flag_status = 0 ) THEN


    BEGIN
    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
    set at_end=0;
    UPDATE bounceback_count
    SET bounceback_count.SoftBounce = SoftBounce + 1,
    bounceback_count.updat = c1rec_updat,
    bounceback_count.bounce_reason = vBounce_reason
    WHERE email = c1rec_email
    AND CAMPAIGN = c1rec_CAMPAIGN
    ;
    END;

    IF (at_end=1) THEN

    INSERT INTO bounceback_count
    ( email ,
    vemail ,
    campaign ,
    reason ,
    reason_count ,
    HardBounce ,
    SoftBounce ,
    Bounce_Reason )
    VALUES ( c1rec_email ,
    LCASE(c1rec_email) ,
    c1rec_campaign ,
    'SoftBounce' ,
    1 ,
    0 ,
    1 ,
    vBounce_reason )
    ;
    END IF ;
    END IF ;

    SELECT REASON
    INTO VAR_REASON2 FROM BOUNCEBACK_COUNT
    WHERE email = c1rec_email
    AND CAMPAIGN = c1rec_CAMPAIGN
    ;

    IF VAR_REASON2 = 'HardBounce' THEN

    SET var_reason2 = 'HardBounce,SoftBounce' ;
    ELSEIF VAR_REASON2 = 'SoftBounce' THEN

    SET var_reason2 = 'SoftBounce' ;
    END IF ;


    BEGIN
    DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
    set at_end=0;
    UPDATE bounceback_count
    SET bounceback_count.REASON_COUNT = REASON_COUNT + 1,
    bounceback_count.REASON = var_reason2
    WHERE email = c1rec_email
    AND CAMPAIGN = c1rec_CAMPAIGN
    ;
    END;

    END LOOP LOOP_LABEL1 ;
    SET c1_OPEN_FLAG = 0;
    CLOSE c1 ;

    END ;
    END p1


    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 "0; DECLARE c1rec_".
    Expected tokens may include: "<space>". LINE NUMBER=27. SQLSTATE=42601

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot use * in a variable name.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •