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

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;

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

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;