Passing a parameter to IN predicate of DB2 udf

[Tracy]

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)
;

db2 "select * from mytable"
NAME GROUP
---------- -----
Peter A
John A
Mary B
Susan C

4 record(s) selected.
db2 "select * from table(myfunc(char('A'))) as t1"
NAME
----------
Peter
John

2 record(s) selected.
db2 "select * from table(myfunc(char('A,B'))) as t1"
NAME
----------

0 record(s) selected.
db2 "select * from table(myfunc(char('''A'',''B'''))) as t1"

NAME
----------

0 record(s) selected.

[Serge Rielau]

Tracy wrote:

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)
;

db2 "select * from mytable"

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

4 record(s) selected.

db2 "select * from table(myfunc(char('A'))) as t1"

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

2 record(s) selected.

db2 "select * from table(myfunc(char('A,B'))) as t1"

NAME
----------

0 record(s) selected.

db2 "select * from table(myfunc(char('''A'',''B'''))) as t1"

The in list sees the parameter as a single VARCHAR, not as a list of
strings. Th eeasiest way to get where you want to be is to use dynamic
SQL which is allowed in SQL Procedures only:

CREATE PROCEDURE myproc(IN cgroup VARCHAR(20))
BEGIN
DECLARE curtxt VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET curtxt = 'SELECT * FROM mytab WHERE group IN (' || cgroup || ')';
PREPARE stmt;
OPEN cur;
END

CALL myproc('''A'', ''B''')

If your groups have a fixed length (say single character for simplicity)
and you want to stick with pure SQL you can try this:

CREATE FUNCTION myfun(cgroup VARCHAR(20))
RETURNS TABLE(name VARCHAR(10))
RETURN
WITH rec(name, num)
AS SELECT name, 1
FROM mytab WHERE group = substr(cgroup, 1, 1)
UNION ALL
SELECT mytab.name, num + 1
FROM mytab, rec
WHERE group = substr(cgroup, NULLIF(num+1, LENGTH(cgroup)), 1)
SELECT name from rec;

SELECT * FROM TABLE(myfun('AB')) AS F;

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

Serge Rielau wrote:

Tracy wrote:

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)
;

db2 "select * from mytable"

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

4 record(s) selected.

db2 "select * from table(myfunc(char('A'))) as t1"

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

2 record(s) selected.

db2 "select * from table(myfunc(char('A,B'))) as t1"

NAME
----------

0 record(s) selected.

db2 "select * from table(myfunc(char('''A'',''B'''))) as t1"

The in list sees the parameter as a single VARCHAR, not as a list of
strings. Th eeasiest way to get where you want to be is to use dynamic
SQL which is allowed in SQL Procedures only:

CREATE PROCEDURE myproc(IN cgroup VARCHAR(20))
BEGIN
DECLARE curtxt VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET curtxt = 'SELECT * FROM mytab WHERE group IN (' || cgroup || ')';
PREPARE stmt;
OPEN cur;
END

CALL myproc('''A'', ''B''')

If your groups have a fixed length (say single character for simplicity)
and you want to stick with pure SQL you can try this:

CREATE FUNCTION myfun(cgroup VARCHAR(20))
RETURNS TABLE(name VARCHAR(10))
RETURN
WITH rec(name, num)
AS SELECT name, 1
FROM mytab WHERE group = substr(cgroup, 1, 1)
UNION ALL
SELECT mytab.name, num + 1
FROM mytab, rec
WHERE group = substr(cgroup, NULLIF(num+1, LENGTH(cgroup)), 1)
SELECT name from rec;

SELECT * FROM TABLE(myfun('AB')) AS F;

Oops:
NULLIF(num+1, LENGTH(cgroup) + 1)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Brian Tkatch]

Even without fixed length, as long as comma is not a valid character:

CREATE FUNCTION myfun(cgroup VARCHAR(20)) RETURNS TABLE(name
VARCHAR(10))
RETURN SELECT name FROM mytable WHERE POSSTR(',' || cgroup || ',',
group) > 0

Well, it's untested (i can't make a FUNCTION on a temp table.)

B.

[Brian Tkatch]

A separate issue.

Are you sure you want the FUNCTION to be DETERMINISTIC?

B.

[tuarek]

Brian,

I guess use of POSSTR wouldn't be easy if Tracy has longer/complex
strings. She may even need "ESCAPE" s...

Regards,

Mehmet

[--CELKO--]

This is a common Newbie design error, usually comitted by people who
learned to program in BASIC instead of a compiled language. How many
parameters can a proceure accept? I don't the limit in DB@, but SQL
Server can have 1024 of them.

CREATE FUNCTION myfunc (cgroup VARCHAR(20))
RETURNS TABLE
(IN foobar_ name VARCHAR(10),
IN p1 INTEGER, IN p2 INTEGER, ..IN pn INTEGER)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECTmfoobar_name
FROM Foobar
WHERE grp_nbr IN (p1, p2, .., pn);

This will avoid dynamic SQL and all the horrible problems you have with
it. You can do this parameter list with a simple text editor.

[Tracy]

Dear Serge, thanks so much for your advice. I would like to stick to
pure SQL this time and my groups are of variable lengths. Anyway, your
suggestion are truly useful for my future reference. =D

[Tracy]

Dear Brian, i am actually quite confused on when to use DETERMINISTIC
or NOT DETERMINISTIC (rather sily :P), any idea on this case?

[Brian Tkatch]

On this case? Do not use it.

From the manual:

===============
This optional clause specifies whether the function always returns the
same results for given argument values (DETERMINISTIC) or whether the
function depends on some state values that affect the results (NOT
DETERMINISTIC). That is, a DETERMINISTIC function must always
return the same table from successive invocations with identical
inputs.
Optimizations taking advantage of the fact that identical inputs always
produce the same results are prevented by specifying NOT
DETERMINISTIC.
NOT DETERMINISTIC must be explicitly or implicitly specified if the
body of the function accesses a special register or calls another
non-deterministic function (SQLSTATE 428C2).
===============

Basically, if the data can change, assume the data will change. And if
it changes, DETERMINISTIC tells the FUNCTION not to care. So, if a
FUNCTION uses a TABLE, DETERMINISTIC should not be used.

B.

[Brian Tkatch]

Oops. The IN paramater needs the commas too.

CREATE FUNCTION myfun(cgroup VARCHAR(20)) RETURNS TABLE(name
VARCHAR(10))
RETURN SELECT name FROM mytable WHERE POSSTR(',' || cgroup || ',', ','
|| group || ',') > 0

B.