Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2010
    Posts
    4

    Question Unanswered: UDF returning table with multiple rows

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Dec 2010
    Posts
    4
    Quote Originally Posted by ARWinner View Post
    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.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Dec 2010
    Posts
    4
    Quote Originally Posted by ARWinner View Post
    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 ??

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Dec 2010
    Posts
    4
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •