Hi,

Please refer to the following code. An external application will call this UDF - RETRIEVE_SEQUENCE where it will pass sequence name as an input parameter to this UDF. The UDF will INVOKE the appropriate sequence based on some logic. When I try INVOKE this sequence within the UDF, I am getting the following error. Please note that the actual sequence that is being passed exists in the database. When the sequence name are passed as parameters (p_sequence_nm), the UDF isn't working. But if I INVOKE the sequence with the actual SEQUENCE NAME, instead of a variable, then the UDF is working fine. How can we resolved this problem?

/* DO GET ERROR */
IF v_hostname1 = v_hostname2 THEN
SET v_seqvalue = NEXTVAL FOR GFOR.pi_sequence_nm + v_seqbase;
END IF;

/* WORKS FINE */
IF v_hostname1 = v_hostname2 THEN
SET v_seqvalue = NEXTVAL FOR GFOR.SQ00CTAP + v_seqbase;
END IF;

Error Message:
Error:6/14/2010 10:47:23 AM 0:00:00.031: DB2 Database Error: ERROR [42704] [IBM][DB2/LINUXX8664] SQL0204N "GFOR.PI_SEQUENCE_NM" is an undefined name. LINE NUMBER=31. SQLSTATE=42704

--CODE SNIPPET OF THE UDF

CREATE FUNCTION GFOR.RETRIEVE_SEQUENCE(pi_sequence_nm VARCHAR(64))
RETURNS INTEGER
LANGUAGE SQL
SPECIFIC GFOR.RETRIEVE_SEQUENCE
NOT DETERMINISTIC
READS SQL DATA
INHERIT SPECIAL REGISTERS


BEGIN ATOMIC

-- Local Variable Declaration
DECLARE v_seqvalue INT;
DECLARE v_hostname1 VARCHAR(20);
DECLARE v_hostname2 VARCHAR(20);
DECLARE v_seqbase INT;
DECLARE v_sequence VARCHAR(15);


SET v_hostname1 = (SELECT HOST_NAME FROM TABLE(SYSPROC.ENV_GET_SYS_INFO()) AS SYSTEMINFO);

--Get the HOSTNAME and sequence_base from the lookup table based on the given host_name
SET (v_hostname2,v_seqbase) = (SELECT server_cd, sequence_base_nbr
FROM GFOR.XREF_SERVER_SEQUENCE_BASE
WHERE server_cd = v_hostname1);



-- Invoke the SEQUENCE passed and Generate the Keys
IF v_hostname1 = v_hostname2 THEN
SET v_seqvalue = NEXTVAL FOR GFOR.pi_sequence_nm + v_seqbase;
END IF;


RETURN v_seqvalue;

END;

This is like a broken record. This question has been answered at least three times. Please look back at your prior posts for the answers on how to resolve running dynamic SQL.
Dave

I am not sure what you mean by broken record. Just give an example of how to resolve this.

Use dynamic SQL.

Can someone show me how to use dynamic sql on this example as I am very new to Db2. I spent enough time and still unable to find a solution.

May be not...

Let me google that for you

Please take a look at one of your first threads on this subject and Stolze told you how to do it at the end of thread.
SQL Queries in DB2 UDF
Dave