UDF returning table with multiple rows

Uncle_g · #1 (**permalink**) 12-16-10, 07:32

Hello,
We are migrating from Firebird / SqlServer to DB2 and we have problem with table function. What we want to achieve is to write UDF (or SP) which will return multiple rows depending on input parameter.
Let me explain it on example (simplified diagram).

func1(@userID integer)
RETURNS TABLE (v1 integer, v2 string, v3 integer)

userTypeID = SELECT userType FROM ......
if (userTypeID = 1) then return .....
elseif (userTypeID = 2) then return .....

end

In Firebird or SqlServer is very simple to achieve, but in db2 I don't have idea how to do it. I tried with temp table (impossible to use in UDF) also with cursor, but with no success

Also I'm trying to achieve more complicated goal.

func1(@userID integer)
RETURNS TABLE (v1 integer, v2 string, v3 integer)

SELECT x1, x2, x3 FROM ......

for
if (x1 = 1) then p = SELECT .....
x2 = x2 + p, etc
INSERT INTO @table(p, x2, x3)
end

return @table
end

Please if You can help me I would be grateful.
Thank You

ARWinner · #2 (**permalink**) 12-16-10, 12:20

It looks like this:

Code:

CREATE FUNCTION MyFunction(arg1 int)
RETURNS TABLE (Col1 int, col2 varchar)
LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
RETURN SELECT COL1,Col2 from MyTable where Col3 = arg1

Andy

Uncle_g · #3 (**permalink**) 12-16-10, 15:29

Quote:

Originally Posted by ARWinner

It looks like this:

Code:

CREATE FUNCTION MyFunction(arg1 int)
RETURNS TABLE (Col1 int, col2 varchar)
LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
RETURN SELECT COL1,Col2 from MyTable where Col3 = arg1

Andy

Thank You Andy, but a returninig resultset is depended on some other value (in this example userTypeID). If userTypeID = 1 then we have 1st query and if userTypeID = 2 then we have another query. And there is a problem.

ARWinner · #4 (**permalink**) 12-16-10, 15:43

You can try something like this, but it is untried:

Code:

CREATE FUNCTION MyFunction(arg1 int)
RETURNS TABLE (Col1 int, col2 varchar)
LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
BEGIN ATOMIC
IF (arg1 = 1)
   THEN RETURN SELECT COL1,Col2 from MyTable where Col3 = arg1;
    ELSE RETURN SELECT COL1,Col2 from MyTable where Col4 = arg1;
END IF;
END@

If that does not work, you can try writing a Stored Procedure to return either result set and then wrap the function around it.

Andy

Uncle_g · #5 (**permalink**) 12-17-10, 05:42

Quote:

Originally Posted by ARWinner

You can try something like this, but it is untried:

Code:

CREATE FUNCTION MyFunction(arg1 int)
RETURNS TABLE (Col1 int, col2 varchar)
LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
BEGIN ATOMIC
IF (arg1 = 1)
   THEN RETURN SELECT COL1,Col2 from MyTable where Col3 = arg1;
    ELSE RETURN SELECT COL1,Col2 from MyTable where Col4 = arg1;
END IF;
END@

If that does not work, you can try writing a Stored Procedure to return either result set and then wrap the function around it.

Andy

Nope, it does not work. I get a message that RETURN must be the last command. Also I don't have idea how to return result set from SP in UDF. Do I have to write CLR in Java or C# in such simple example ??

ARWinner · #6 (**permalink**) 12-17-10, 09:10

The SP would be like:

Code:

CREATE PROCEDURE MyProc(IN arg1 INT)
SPECIFIC MyProc
DYNAMIC RESULT SETS 1
BEGIN
   DECLARE CURSRO1 WITH RETURN FOR
   SELECT COl1,Col2 from MyTable where Col3 = arg1;

   DECLARE CURSOR2 WITH RETURN FOR
   SELECT Col1,Col2 From MyTable where Col4 = arg1;

   IF (Arg1 = 1)
     THEN OPEN CURSOR1;
     ELSE OPEN CURSOR2;
   END IF;
END@

Then the UDF would be like:

Code:

CREATE FUNCTION MyFunction(arg1 int)
RETURNS TABLE (Col1 int, col2 varchar)
LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
BEGIN ATOMIC
   RETURN Call MyProc;
END@

Andy

Uncle_g · #7 (**permalink**) 12-20-10, 09:14

Thank You very much Andy for the example. I have modified and create SP with no problem, but still I have a big problem with creating UDF which will return a resultset from SP. Here is my code for UDF:

Code:

CREATE FUNCTION Func1
  ("ARG_IN" INTEGER)
  RETURNS TABLE
  (ARG_1 INTEGER,
    ARG_2 VARCHAR(30),
    ARG_3 INTEGER)
  SPECIFIC Func1
LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
BEGIN ATOMIC
    RETURN CALL SP_TEST(ARG_IN);
END@

I get error(SQL0104N) and SQLSTATE=42601. I need that UDF because I want to use it in JOIN in other queries.
Thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech