Environment : DB2 UDB V8.2/AIX 5.3

Background : Application migration from Oracle to DB2

How to code an equivalent DB2 UDF having a dynamic sql as an argument to get the count of rows present in the table?

Equivalent oracle UDF is as below
create or replace FUNCTION row_count_check (sql1 IN VARCHAR2)
RETURN NUMBER IS
Count_NO NUMBER;
BEGIN
EXECUTE IMMEDIATE sql1 INTO count_no;
RETURN (count_no);
END;

Why dont you pass the tablename instead of the query and then execute the same.


you can follow the following steps

1. Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR statement1

2. Create the statement
SQLQuery = 'SELECT Count(*) FROM ' || TableName ( where TableName is the variable which you have passed )

3. Prepare the statement
PREPARE statement1 from SQLQuery

4. Open cursor
OPEN cursor1

5. Put the value into a variable
FETCH cursor1 into TableCount

6. Finally Return the count
RETURN TableCount

Dynamic SQL is not possible in UDFs. You have to use a stored procedure for such a task.

Hi Knut.....I looked at one of your other recent replies where you have suggested to use Dynamic Statement in UDF, so does that means the new version allows this functionality:

What I am struggling with is to use CASE with Multiple Select Statements in UDF ....Not sure if that's possible or if that can be achecived using some other efficient way:

The Function I am trying is, accepts two parameter and based on one of the parameter TYPE select from a specific table.......
CREATE FUNCTION TEST.GET_CHILD_ID(
IN_ID BIGINT,
IN_TYPE BIGINT
)
RETURNS BIGINT
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
RETURNS NULL ON NULL INPUT
BEGIN ATOMIC
DECLARE CHILD_ID BIGINT;
CASE
WHEN IN_TYPE=1 or IN_TYPE=11
THEN SELECT TABLE_A.ID INTO CHILD_ID FROM TEST.TABLE_A AS TABLE_A WHERE TABLE_A.TABLE_A_ID = IN_ID;
WHEN IN_TYPE=4 or IN_TYPE=41
THEN SELECT TABLE_B.ID INTO CHILD_ID FROM TEST.TABLE_B AS TABLE_B WHERE TABLE_B.TABLE_B_ID = IN_ID;
WHEN IN_TYPE=2 or IN_TYPE=21
THEN SELECT TABLE_C.ID INTO CHILD_ID FROM TEST.TABLE_C AS TABLE_C WHERE TABLE_C.TABLE_C = IN_ID;
WHEN IN_TYPE=0 or IN_TYPE=111
THEN SELECT TABLE_D.ID FROM INTO CHILD_ID TEST.TABLE_D AS TABLE_D WHERE TABLE_D.TABLE_D_ID = IN_ID;
WHEN IN_TYPE=9 or IN_TYPE=91
THEN SELECT TABLE_E.ID INTO CHILD_ID FROM TEST.TABLE_E AS TABLE_E WHERE TABLE_E.TABLE_E_ID = IN_ID;
END CASE;
RETURN CHILD_ID;
END@


CASE STATEMENT IN UDF : Error Code 42601

Scalar SQL UDFs do not support dynamic SQL. You can use a so-called "Compound SQL (Dynamic) statement" and there is no OPEN/FETCH/CLOSE allowed in the function body. You can use a cursor in a table function, however.

I guess I am using the Compound SQL in my function.....Please correct me if I am wrong. I have tried writing this Function in all the possible ways but somehow I am stuck with this.......just can't make it work.

You are using a CASE statement inside the compound SQL (BEGIN ATOMIC ... END), which is not supported because the syntax diagram does not list it as allowed statement. dav1mo mentioned that in the other thread you are referencing above. I suggest you rewrite your UDF implementation as Dave proposed.
If you think that your code should actually work, please explain why you believe the DB2 documentation supports that.

I have a code example here.


IBM V91 for Z/OS:

CREATE FUNCTION TEST.FN_T2( )
RETURNS DATE FOR SBCS DATA CCSID EBCDIC
PARAMETER CCSID EBCDIC
LANGUAGE SQL
READ SQL DATA
BEGIN ATOMIC
RETURN SELECT DATE(CURRENT TIMESTAMP)
FROM SYSIBM.SYSDUMMY1;

I am getting this error message..
Error:22/02/2011 14:15:09 0:00:00.015: DB2 Database Error: ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "FOR" was found following "". Expected tokens may include: ", ) <END-OF-CLAUSE>". SQLSTATE=42601

The syntax doesn't fit. You have a BEGIN ATOMIC but no matching END. But you don't need the BEGIN ATOMIC anyway. Just use the RETURN statement as the whole body of the function.