I'm having all manner of problems trying to create a UDF that contains
a simple CASE statement in Express-C 9.7.4.

Say for example the following:

CREATE FUNCTION GET_DATA(V_ACTION CHAR(1), V_ACT_DATE DATE)
RETURNS TABLE ( ID INTEGER,
FIELD_1 INTEGER,
FIELD_2 INTEGER)
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC

DECLARE V_SET_DATE DATE;

CASE WHEN V_ACTION = 'C' THEN SET V_SET_DATE = (SELECT ACT_DATE
FROM DATE_TABLE WHERE D2_DATE = V_ACT_DATE);
WHEN V_ACTION = 'A' THEN SET V_SET_DATE = V_ACT_DATE;
END CASE;

RETURN
SELECT ID, FIELD_1, FIELD_2
FROM TABLE_1
WHERE ACT_DATE = V_SET_DATE;

END !
=======================

Now if I use an IF statement it works fine, but fails with the above
CASE syntax. I've tried reading up on restrictions but get all
confused when it starts talking about SQL/PL, compound inline sql,
compound compiled sql etc and frankly I have no idea what the
difference is.

I couldn't tell you what the above is and would love to know how one
differs from the other. Why though would the above CASE statement fail
is my main concern.

Many thanks for any help, Fin.

Please try to specify "BEGIN NOT ATOMIC".

There is the following statement in "Description" of "Compound SQL
(compiled)" in "DB2 SQL Reference Volume 2".

statement is processed as a compound SQL (inlined) statement. <<

You can't use CASE statement in a compound SQL (inlined).


Moreover, it is not neccesary to use CASE statement in your GET_DATA
UDF.

Example 1:
(by using CASE expression)
CREATE FUNCTION GET_DATA(V_ACTION CHAR(1), V_ACT_DATE DATE)
RETURNS TABLE ( ID INTEGER
, FIELD_1 INTEGER
, FIELD_2 INTEGER
)
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC

RETURN
SELECT ID, FIELD_1, FIELD_2
FROM TABLE_1
WHERE ACT_DATE
= CASE
WHEN V_ACTION = 'C' THEN
(SELECT ACT_DATE
FROM DATE_TABLE
WHERE D2_DATE = V_ACT_DATE
)
WHEN V_ACTION = 'A' THEN
V_ACT_DATE
END
!

Example 2:
RETURN
SELECT ID, FIELD_1, FIELD_2
FROM TABLE_1
WHERE V_ACTION = 'C'
AND ACT_DATE
= (SELECT ACT_DATE
FROM DATE_TABLE
WHERE D2_DATE = V_ACT_DATE
)
OR V_ACTION = 'A'
AND ACT_DATE = V_ACT_DATE

Example 3:
RETURN
SELECT t1.ID, t1.FIELD_1, t1.FIELD_2
FROM TABLE_1 AS t1
LEFT OUTER JOIN
DATE_TABLE AS dt
ON V_ACTION = 'C'
AND t1.ACT_DATE = dt.ACT_DATE
AND dt.D2_DATE = V_ACT_DATE
WHERE dt.ACT_DATE IS NOT NULL
OR V_ACTION = 'A'
AND t1.ACT_DATE = V_ACT_DATE

Thanks for the help, much appreciated as always.

On 5/24/2011 9:59 AM, Fin wrote:
sort out the differences between inline and non inline SQL PL

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau