Dear all,

I have encountered a problem in passing a VARCHAR parameter (which is a
list of accepted values) to the IN predicate of a DB2 user-defined
function.

For example, I have a table (mytable) storing ppl names & their
corresponding groups. I would like to create a UDF (myfunc) that select
a list of ppl names from (mytable) who belong to some dynamic choices
of groups. I have tried the following codings but it doesn't work if I
try to provide a list of designated choices of groups as a single
parameter.

Can anyone help? Thanks a lot!

Tracy

////Reference SQLs:

CREATE TABLE mytable (
    name VARCHAR(10),
    group CHAR(1)
);

INSERT into mytable values ('Peter','A');
INSERT into mytable values ('John','A');
INSERT into mytable values ('Mary','B');
INSERT into mytable values ('Susan','C');

CREATE FUNCTION myfunc (cgroup VARCHAR(20))
RETURNS TABLE
(
       name VARCHAR(10)
)

LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN

       SELECT
               name
       FROM
               mytable
       where
               group in (cgroup)
;

NAME       GROUP
---------- -----
Peter      A
John       A
Mary       B
Susan      C

 4 record(s) selected.

NAME
----------
Peter
John

 2 record(s) selected.

NAME
----------

 0 record(s) selected.

NAME      
----------

 0 record(s) selected.