Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    4

    Unanswered: DB2 Create Function with a recursive CTE fails with an SQLCODE=-104

    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.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by kevinhurt
    Everytime I try to create a UDF with recursion I get a SQL syntax error of -104 complaining about a "(" was encountered.
    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:
    Code:
    CREATE FUNCTION TEST ()
    RETURNS INT
    DETERMINISTIC
    RETURN (SELECT 5 FROM SYSIBM.SYSDUMMY1) ;
    as opposed to the completely equivalent but accepted
    Code:
    CREATE FUNCTION TEST ()
    RETURNS INT
    DETERMINISTIC
    RETURN 5 ;
    In my case the error message is:
    Code:
    DSNT408I SQLCODE = -214, ERROR:  AN EXPRESSION IN THE FOLLOWING POSITION, OR
    STARTING WITH 0 IN THE RETURN CLAUSE IS NOT VALID.
    REASON CODE = 7
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jul 2006
    Posts
    4

    Thanks

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •