Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2010
    Posts
    7

    Unanswered: CASE STATEMENT IN UDF : Error Code 42601

    I am having a big time trouble ....keep getting 42601.

    Simple scenario: Pass two parameters ID & TYPE based on TYPE select ID from 1 of the 5 Tables Table_A............Table_E -----> Return ID

    CREATE FUNCTION TEST.GET_CHILD_ID(
    IN_ID BIGINT,
    IN_TYPE BIGINT
    )
    RETURNS BIGINT
    LANGUAGE SQL
    SPECIFIC TRANSR11.GET_TARGETVALUE
    DETERMINISTIC
    READS SQL DATA
    RETURNS NULL ON NULL INPUT
    BEGIN ATOMIC
    DECLARE CHILD_ID BIGINT;
    CASE
    WHEN IN_TYPE=1
    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
    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
    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
    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
    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;

    What am I doing wrong, your help would really be appreciated.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    incorrect usage of a case statement.
    How about this instead?

    Code:
    SELECT TABLE_A.ID 
        FROM TEST.TABLE_A AS TABLE_A 
    WHERE TABLE_A.TABLE_A_ID = IN_ID
      and IN_TYPE=1
    Union
    SELECT TABLE_B.ID 
        FROM TEST.TABLE_B AS TABLE_B 
    WHERE TABLE_B.TABLE_B_ID = IN_ID
      and IN_TYPE=4
    Union
    SELECT TABLE_C.ID 
        FROM TEST.TABLE_C AS TABLE_C 
    WHERE TABLE_C.TABLE_C = IN_ID 
      and IN_TYPE=2
    Union
    SELECT TABLE_D.ID 
        FROM TEST.TABLE_D AS TABLE_D 
    WHERE TABLE_D.TABLE_D_ID = IN_ID 
      and IN_TYPE=0
    Union
    SELECT TABLE_E.ID 
        FROM TEST.TABLE_E AS TABLE_E
    WHERE TABLE_E.TABLE_E_ID = IN_ID
      and IN_TYPE=9
    Dave Nance

  3. #3
    Join Date
    Nov 2010
    Posts
    7
    I'll not agree if it's a incorrect use of UDF. I am trying to get a scalar value based on the input and UDF's are meant for that.

    I can not use UNION as there's a big possibility that the same ID exist in more than one table and there's no TYPE column in those tables.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by GKUMA View Post
    I'll not agree if it's a incorrect use of UDF.
    You may or may not agree with it, but it's a fact. There are limits on what statements you can use in a UDF, depending on the version and the UDF type (inlined vs. compiled). Try removing the ATOMIC option.

  5. #5
    Join Date
    Nov 2010
    Posts
    7
    So does that means DB2 does not support CASE construct in UDF........or my sintaxes are not correct.

    Is there any other way to acheive the same.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    as I said, its incorrect usage of a CASE statement. CASE is part of an SQL statement, not a standalone construct(yet?). Your condition can still be met in an sql statement, just need to change what I gave you around a little bit.

    Code:
    select child.id from (
      SELECT TABLE_A.ID , 1 as type
          FROM TEST.TABLE_A AS TABLE_A 
      WHERE TABLE_A.TABLE_A_ID = IN_ID
      Union
      SELECT TABLE_B.ID, 4 as type
          FROM TEST.TABLE_B AS TABLE_B 
      WHERE TABLE_B.TABLE_B_ID = IN_ID
      Union
      SELECT TABLE_C.ID, 2 as type
          FROM TEST.TABLE_C AS TABLE_C 
      WHERE TABLE_C.TABLE_C = IN_ID 
      Union
      SELECT TABLE_D.ID, 0 as type
          FROM TEST.TABLE_D AS TABLE_D 
      WHERE TABLE_D.TABLE_D_ID = IN_ID 
      Union
      SELECT TABLE_E.ID, 9 as type
          FROM TEST.TABLE_E AS TABLE_E
      WHERE TABLE_E.TABLE_E_ID = IN_ID) as child
    where child.type = IN_TYPE
    Dave Nance

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dav1mo View Post
    CASE is part of an SQL statement, not a standalone construct(yet?).
    Quote Originally Posted by Albert Nimzicki
    Uh... Mr. President. That's not entirely accurate.
    You may want to check the manual, examples in particular...

    CASE statement

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Thanks Nick. I never noticed when that was introduced.

    Now knowing that, I would say that the original problem is probably due to your termination character. You may want to change that to a tilde or something.

    Dave nance

  9. #9
    Join Date
    Nov 2010
    Posts
    7
    I have tried it doing both ways using If ....elseif and case with a different termination char but keep getting th errors.

    --#SET TERMINATOR @

    CREATE FUNCTION TEST.GET_CHILD_ID(
    IN_ID BIGINT,
    IN_TYPE BIGINT
    )
    RETURNS BIGINT
    LANGUAGE SQL
    SPECIFIC TRANSR11.GET_TARGETVALUE
    DETERMINISTIC
    READS SQL DATA
    RETURNS NULL ON NULL INPUT
    BEGIN ATOMIC
    DECLARE CHILD_ID BIGINT;
    CASE
    WHEN IN_TYPE=1
    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
    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
    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
    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
    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@



    END@

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't see a system in your use of statement terminators. CREATE FUNCTION is one statement and as such must be terminated so as to indicate to the command line processor where that statement ends.

  11. #11
    Join Date
    Nov 2010
    Posts
    7
    there's only 1 end@ statement the other one is just a typo.

Tags for this Thread

Posting Permissions

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