Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    11

    Unanswered: CASE statement in DB2 UDF

    Hi,

    I am new to DB2 Sql programming. I have the following UDF. How can I use the case statement within UDF?

    I get the following error:

    DB2 Database Error: ERROR [42601] [IBM][DB2/LINUXX8664] SQL0104N An unexpected token "CASE pi_table_name WHEN 'CTRL_APPROVA" was found following "******** */ ". Expected tokens may include: "<space>". LINE NUMBER=34. SQLSTATE=42601


    CODE SNIPPET:

    CREATE FUNCTION GFOR.RETRIEVE_SEQUENCE(pi_table_nm VARCHAR(64))
    RETURNS INTEGER
    LANGUAGE SQL
    CONTAINS SQL
    NO EXTERNAL ACTION
    DETERMINISTIC
    SPECIFIC GFOR.RETRIEVE_SEQUENCE
    NULL CALL
    INHERIT SPECIAL REGISTERS



    BEGIN ATOMIC

    -- Declarations and Initilizations
    DECLARE v_seq_mask INT default 0; -- Will be updated to 1,000,000,000 in DR data center
    DECLARE v_seq_value INT;
    DECLARE v_servername VARCHAR(20);
    DECLARE v_servertype VARCHAR(10);
    DECLARE v_seqname VARCHAR(10);
    DECLARE v_seqbase INT;

    SET v_servername = CURRENT SERVER;

    /*
    ************************************************** **************
    ** Get the appropriate sequence name based on the table name **
    ** passed to this function **
    ************************************************** **************
    */


    CASE pi_table_name
    WHEN 'CTRL_APPROVAL' THEN SET v_seqname = 'SQ00CTAP';
    WHEN 'CTRL_EVENT' THEN SET v_seqname = 'SQ00CTEV';
    WHEN 'CTRL_RESULT_RECORD' THEN SET v_seqname = 'SQ00CTRERE';
    WHEN 'DATA_MOVEMENT_CONTROL' THEN SET v_seqname = 'SQ00DAMOCO';
    WHEN 'DATA_MOVEMENT_ERROR' THEN SET v_seqname = 'SQ00DAMOER';
    WHEN 'EXP_CONFIGURATION' THEN SET v_seqname = 'SQ00EXCO';
    WHEN 'EXP_EVENT' THEN SET v_seqname = 'SQ00EXEV';
    WHEN 'EXP_EVENT_ERROR' THEN SET v_seqname = 'SQ00EXEVER';
    WHEN 'FO_EVENT_LOG' THEN SET v_seqname = 'SQ00FOEVLO';
    WHEN 'FO_USER_COMMENT' THEN SET v_seqname = 'SQ00FOUSCO';
    WHEN 'IMP_CONFIGURATION' THEN SET v_seqname = 'SQ00IMCO';
    WHEN 'IMP_EVENT' THEN SET v_seqname = 'SQ00IMEV';
    WHEN 'IMP_EVENT_ERROR' THEN SET v_seqname = 'SQ00IMEVER';
    WHEN 'PR_CORRECTION_EVENT' THEN SET v_seqname = 'SQ00PRCOEV';
    WHEN 'PR_FUND_DIVIDEND_CORRECTION' THEN SET v_seqname = 'SQ00PRFUDICO';
    WHEN 'PR_FUND_DIVIDEND_STAGE' THEN SET v_seqname = 'SQ00PRFUDIST';
    WHEN 'PR_FUND_VALUATION_CORRECTION' THEN SET v_seqname = 'SQ00PRFUVACO';
    WHEN 'PR_FUND_VAL_DIV_STG_APPROVAL' THEN SET v_seqname = 'SQ00PRFUVADISTAP';
    WHEN 'PR_FUND_VALUATION_ESTIMATE' THEN SET v_seqname = 'SQ00PRFUVAES';
    WHEN 'PR_FUND_VALUATION_EST_DETAIL' THEN SET v_seqname = 'SQ00PRFUVAESDE';
    WHEN 'PR_FUND_VALUATION_STAGE' THEN SET v_seqname = 'SQ00PRFUVAST';
    END CASE;

    /*
    ************************************************** **************
    ** Need code section to retrieve Production or DR Servername, **
    ** and sequence_base based on value in v_servername **
    ************************************************** **************
    */
    SET v_servertype = 'PROD_server';
    SET v_seqbase = 0;

    IF v_servertype = 'PROD_server' THEN


    ELSE
    END IF;

    if

    RETURN seq_value;

    END;

    Thanks,
    Subra

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    SET v_seqname = case pi_table_name when ...

  3. #3
    Join Date
    Jun 2010
    Posts
    11
    Thanks. That works. Now have the following code snippet. Will the sequence return the value? I am getting below error:

    Error 6/10/2010 10:06:59 AM 0:00:00.031 DB2 Database Error: ERROR [42704] [IBM][DB2/LINUXX8664] SQL0204N "GFOR.V_SEQNAME" is an undefined name. LINE NUMBER=76. SQLSTATE=42704
    1 0


    IF v_servertype = 'PROD_server' THEN
    SET v_seqvalue = (nextval for v_seqname) + v_seqbase;
    END IF;

    RETURN v_seqvalue;

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    there is no sequence with that schema name in your system. Seems like you might need to either change the current sqlid or qualify the sequence name.
    Dave

  5. #5
    Join Date
    Jun 2010
    Posts
    11
    Thanks for your reply. The sequence name is derived from the following code. Please se below. I am still getting the same error.

    CREATE FUNCTION GFOR.RETRIEVE_SEQUENCE(pi_table_nm VARCHAR(64))
    RETURNS INTEGER
    LANGUAGE SQL
    CONTAINS SQL
    NO EXTERNAL ACTION
    DETERMINISTIC
    SPECIFIC GFOR.RETRIEVE_SEQUENCE
    NULL CALL
    INHERIT SPECIAL REGISTERS


    BEGIN ATOMIC

    -- Declarations and Initilizations
    DECLARE v_seqmask INT default 0; -- Will be updated to 1,000,000,000 in DR data center
    DECLARE v_seqvalue INT;
    DECLARE v_servername VARCHAR(20);
    DECLARE v_servertype VARCHAR(10);
    DECLARE v_seqname VARCHAR(10);
    DECLARE v_seqbase INT;

    SET v_servername = CURRENT SERVER;

    /*
    ************************************************** **************
    ** Get the appropriate sequence name based on the table name **
    ** passed to this function **
    ************************************************** **************
    */

    SET v_seqname = (CASE pi_table_nm
    WHEN 'CTRL_APPROVAL' THEN 'GFOR.SQ00CTAP'
    WHEN 'CTRL_EVENT' THEN 'GFOR.SQ00CTEV'
    WHEN 'CTRL_RESULT_RECORD' THEN 'GFOR.SQ00CTRERE'
    WHEN 'DATA_MOVEMENT_CONTROL' THEN 'GFOR.SQ00DAMOCO'
    WHEN 'DATA_MOVEMENT_ERROR' THEN 'GFOR.SQ00DAMOER'
    WHEN 'EXP_CONFIGURATION' THEN 'GFOR.SQ00EXCO'
    WHEN 'EXP_EVENT' THEN 'GFOR.SQ00EXEV'
    WHEN 'EXP_EVENT_ERROR' THEN 'GFOR.SQ00EXEVER'
    WHEN 'FO_EVENT_LOG' THEN 'GFOR.SQ00FOEVLO'
    WHEN 'FO_USER_COMMENT' THEN 'GFOR.SQ00FOUSCO'
    WHEN 'IMP_CONFIGURATION' THEN 'GFOR.SQ00IMCO'
    WHEN 'IMP_EVENT' THEN 'GFOR.SQ00IMEV'
    WHEN 'IMP_EVENT_ERROR' THEN 'GFOR.SQ00IMEVER'
    WHEN 'PR_CORRECTION_EVENT' THEN 'GFOR.SQ00PRCOEV'
    WHEN 'PR_FUND_DIVIDEND_CORRECTION' THEN 'GFOR.SQ00PRFUDICO'
    WHEN 'PR_FUND_DIVIDEND_STAGE' THEN 'GFOR.SQ00PRFUDIST'
    WHEN 'PR_FUND_VALUATION_CORRECTION' THEN 'GFOR.SQ00PRFUVACO'
    WHEN 'PR_FUND_VAL_DIV_STG_APPROVAL' THEN 'GFOR.SQ00PRFUVADISTAP'
    WHEN 'PR_FUND_VALUATION_ESTIMATE' THEN 'GFOR.SQ00PRFUVAES'
    WHEN 'PR_FUND_VALUATION_EST_DETAIL' THEN 'GFOR.SQ00PRFUVAESDE'
    WHEN 'PR_FUND_VALUATION_STAGE' THEN 'GFOR.SQ00PRFUVAST'
    END);


    /*
    ************************************************** **********************
    ** Need code section to retrieve Production or DR Servername, **
    ** and sequence_base based on value in v_servername **
    ** SET (v_dbname, v_seqbase) = (SELECT database_name, sequence_base **
    ** FROM SERVERNAME **
    ** WHERE database_name = 'PROD_server'); **
    ************************************************** **********************
    */

    SET v_servertype = 'PROD_server';
    SET v_seqbase = 0;

    /*
    ************************************************** ************************
    ** Code section to call appropriate sequence based on table name passed **
    ** and the server type (PROD_server [OR] DR_server' **
    ************************************************** ************************
    */
    IF v_servertype = 'PROD_server' THEN
    SET v_seqvalue = (nextval for v_seqname) + v_seqbase;
    END IF;



    RETURN v_seqvalue;

    END;

Posting Permissions

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