Hi i am trying to return a table from function my input parameter is list, i am not getting result set, query returns no data
Example

CREATE FUNCTION test (para char(100))
RETURNS TABLE (col1 char(7) )
NO EXTERNAL ACTION
F1: BEGIN ATOMIC
RETURN select col1 from admin.table1 where col2 in (para) ;
END
para = 100,101

result 0 records

expected result
Col1
A
B

if para = 100

result
Col1
A

if para = 101

result
Col1
B

Thanks

I am using DB2 v9.5

Why would you expect that?


Wrong. para = '100,101' (with 93 trailing blanks, by the way, because you used CHAR instead of VARCHAR). I hope you know what a character string is.

As a result, your query looks like this:
Consider using dynamic SQL. Check EXECUTE IMMEDIATE in the manual.

If no comma(',') in col2 was guaranteed,
try

CREATE FUNCTION test (para char(100))
RETURNS TABLE (col1 char(7) )
NO EXTERNAL ACTION
RETURN select col1 from admin.table1 where LOCATE(col2 , para) > 0
;

"BEGIN ... END" is not necessary.

Although this is logically correct, how likely is that that you'd use this in real life as opposed to a dynamically constructed statement? I'm sure you realize potential performance issues.

Thanks for the reply, the dynamic sql did not work. but locate is working fine.