Hi,

I am vnew DB2 SQL Programming. I am in the process of writing an UDF. Below is the code snippet. When I try to compile I get the following error. How can I resolve this?

Error Message: Error:6/10/2010 11:40:41 AM 0:00:00.031: DB2 Database Error: ERROR [42704] [IBM][DB2/LINUXX8664] SQL0204N "GFOR.V_SEQNAME" is an undefined name. LINE NUMBER=79. SQLSTATE=42704


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_seqmask INT default 0; -- Will be updated to 1,000,000,000 in DR data center
DECLARE v_seqvalue INT;
DECLARE v_servername VARCHAR(25);
DECLARE v_servertype VARCHAR(10);
DECLARE v_seqname VARCHAR(10);
DECLARE v_seqbase INT;
DECLARE v_table_nm VARCHAR(64);

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

As explained in your other thread, you are attempting to access an object that does not exist on your machine. the line is attempting to call the object which does not exist.
Dave

Dave,

I do understand that v_seqname is not the actual sequence name. As you can see in the code v_seqname is local variable within this function. This variable will be assigned a value of a sequence name based on input pramater p_table_nm. This section is coded as part of the CASE statement. Are you telling me that v_seqname is not evaluating to a value of sequence name? If so, how what would be the best approach?

You may want to use dynamic SQL.