I'm trying to create a new user defined function under DB2 V8.1 for z/OS that includes a recursive common table expression but everytime I try to create it I get a SQL syntax error of -104 complaining about a "(" was encountered. I scoured the SQL for a syntax error and even used an example out of a book and got the same error. I am executing the SQL under DSNTEP2. Here is my SQL:

CREATE FUNCTION LODCN (INDCN CHAR(8))
RETURNS CHAR(8)
NOT DETERMINISTIC
RETURN
WITH
XREFTREE (DCN, CROSS_REF_DCN, DEPTH)
AS (
SELECT ROOT.DCN,
ROOT.CROSS_REF_DCN,
0
FROM MMIT_CROSS_REF ROOT
WHERE ROOT.DCN = INDCN
OR ROOT.CROSS_REF_DCN = INDCN
UNION ALL
SELECT CHILD.CROSS_REF_DCN,
CHILD.DCN,
PARENT.DEPTH + 1 FROM
FROM XREFTREE PARENT INNER JOIN MMIT_CROSS_REF CHILD
ON PARENT.DCN=CHILD.CROSS_REF_DCN
WHERE
PARENT.DEPTH < 5
)
SELECT MIN(DCN)
FROM XREFTREE
;
The error is:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: ON AFTER <INTEGER>

Anybody have any insight?

Kevin

Anybody have any ideas.

Although DB2 v8 for z/OS promises that "one can use a scalar fullselect wherever an expression is allowed", this is not the case for the return value of a UDF; e.g., the following simplified UDF creation statement gives a similar error:as opposed to the completely equivalent but acceptedIn my case the error message is:

Hey, thanks for the information. I haven't read about any UDF limitations about not being able to use a select. I wonder if this is something that works in V9? I guess I will have to find a different way to pass a variable into a CTE.

Thanks
Kevin