Returning user defined datatype from stored function.

[Sameer Deshpande]

How do I create and return user defined data types in DB2.

F.ex

In Oracle I can create a user define datatype and return this data
type from stored function. How can I do the same in DB2?

SELECT id, SYSDATE
FROM t
WHERE id IN ( SELECT * FROM THE ( SELECT CAST( fn( :1 ) AS mydatatype
) FROM dual ) );

The above statements helps me to use bind variables for IN clause SQL
statement. As the number of IN LIST items are dynamic, I can not
hard-code number of bind variables. So I use a database function which
returns me signle element (splits by comma internally). So I have only
one bind variable which gets value from my stored function.

How to do the same in DB2? How can I write DB2 compatible query?

Thanks

Sameer

[Serge Rielau]

What you ask for here look like a user defined distinct type.
E.g.
CREATE DISTINCT TYPE commalist AS VARCHAR(2000) WITH COMPARISONS;
Teh most efficient woudl probably be to write a SQL Table function
which recursively parses teh list and spits out the elements.
An external table UDF (written e.g. in C) certainly would do the trick
as well.

The query would then look like:
SELECT id, CURRENT DATE
FROM t
WHERE id IN (SELECT id FROM TABLE(THE(CAST( ? AS commalist)) AS THE)

The function;
CREATE FUNCTION THE(list COMMALIST) RETURNS TABLE(id INTEGER)
RETURN WITH rec(list, id, iter AS (VALUES (...., 1)
UNION ALL
SELECT ...., iter + 1 FROM rec
WHERE LENGTH(iter) > 0 AND iter < 2000)
SELECT id FROM rec;

Teh magc is in the .... ;-) A bunch of SUBSTR() POSSTR() and INTEGER()
should do it...

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

What you ask for here look like a user defined distinct type.
E.g.
CREATE DISTINCT TYPE commalist AS VARCHAR(2000) WITH COMPARISONS;
Teh most efficient woudl probably be to write a SQL Table function
which recursively parses teh list and spits out the elements.
An external table UDF (written e.g. in C) certainly would do the trick
as well.

The query would then look like:
SELECT id, CURRENT DATE
FROM t
WHERE id IN (SELECT id FROM TABLE(THE(CAST( ? AS commalist)) AS THE)

The function;
CREATE FUNCTION THE(list COMMALIST) RETURNS TABLE(id INTEGER)
RETURN WITH rec(list, id, iter AS (VALUES (...., 1)
UNION ALL
SELECT ...., iter + 1 FROM rec
WHERE LENGTH(iter) > 0 AND iter < 2000)
SELECT id FROM rec;

Teh magc is in the .... ;-) A bunch of SUBSTR() POSSTR() and INTEGER()
should do it...

You can have a look here to get the idea and an example:

http://www-106.ibm.com/developerwork...03stolze1.html
Back to the original question: have you considered using temporary tables?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Sameer Deshpande]

Thanks Serge...

This is exactly what I am looking for..

In a while loop I can split the input string, but how do I append
individual item to table? I need to insert this in the dynamic table
which I want to return...

Is it possible to iterate in SQL statement whos results will be
returned as a table???

F. ex:

In Oracle, I do..

CREATE OR REPLACE TYPE myTableType AS TABLE OF NUMBER;

CREATE OR REPLACE FUNCTION fn_mystrtbl ( p_str in varchar2 ) RETURN
myTableType
AS
l_str LONG DEFAULT p_str || ',';
l_n NUMBER;
l_data myTableType := myTableType ();
BEGIN
LOOP
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(sub str(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
END LOOP;
RETURN l_data;
END;
/

and the same old statement to return the data!! If you see the
function, I append each item to item to l_data() which is a table..

Thanks

Sameer

[Sameer Deshpande]

Thanks Knut!!

http://www-106.ibm.com/developerwork...03stolze1.html
This did my job!!!
Back to the original question: have you considered using temporary tables?

temp table?? I do not need temporary table here as I am not doing any
session specific thing.. May be I put the question in a different way
before... :-(

I use this for IN LIST SQL statements where usage of bind variables is
little tricky... I use this to make use of bind variables so my IN
clause SQL will not get parsed often!!

Thanks

Sameer