CASE STATEMENT IN UDF : Error Code 42601

GKUMA · #1 (**permalink**) 11-15-10, 14:16

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.

dav1mo · #2 (**permalink**) 11-15-10, 15:58

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

GKUMA · #3 (**permalink**) 11-15-10, 16:06

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.

n_i · #4 (**permalink**) 11-15-10, 18:20

Quote:

Originally Posted by GKUMA

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.

GKUMA · #5 (**permalink**) 11-15-10, 22:52

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.

dav1mo · #6 (**permalink**) 11-16-10, 08:45

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

n_i · #7 (**permalink**) 11-16-10, 09:46

Quote:

Originally Posted by dav1mo

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

dav1mo · #8 (**permalink**) 11-16-10, 11:43

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

GKUMA · #9 (**permalink**) 11-16-10, 17:08

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@

n_i · #10 (**permalink**) 11-16-10, 17:54

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.

GKUMA · #11 (**permalink**) 11-17-10, 11:51

there's only 1 end@ statement the other one is just a typo.

Internet Services

Web Development

Digital Marketing

Consumer Tech