Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unhappy Unanswered: Resolved - Want to return multiple integers from a table

    Hi guys,

    I am trying to return more than one values from a table. I have tried Return Table type, Cursor and none of these worked, i couldnt even run function i dont know why.

    Here is my function works well if there is a 1 return;

    CREATE FUNCTION "MAXIMO"."TEST" ()
    RETURNS INTEGER
    LANGUAGE SQL

    BEGIN ATOMIC
    DECLARE v_rowid INTEGER;
    SET v_rowid = (select locid from locclas where location = 'GERMANY' and classificationid = 'INCIDENT')
    RETURN v_rowid;
    END;

    When i want to set v_rowid, if there is more than 1 rows, it gives me an error.
    I wonder how can i retrieve more than 1 values into v_rowid and return those values.

    Thanks.
    Last edited by crozwise; 04-19-11 at 07:30.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It is better to remove double quotations.

    CREATE FUNCTION MAXIMO.TEST ()
    RETURNS TABLE (locid INTEGER)
    LANGUAGE SQL
    RETURN select locid from locclas where location = 'GERMANY' and classificationid = 'INCIDENT'
    ;

  3. #3
    Join Date
    Apr 2011
    Posts
    3
    Quote Originally Posted by tonkuma View Post
    It is better to remove double quotations.

    CREATE FUNCTION MAXIMO.TEST ()
    RETURNS TABLE (locid INTEGER)
    LANGUAGE SQL
    RETURN select locid from locclas where location = 'GERMANY' and classificationid = 'INCIDENT'
    ;
    I got the error again:

    DB2 Database Error: ERROR [42887] [IBM][DB2/LINUXX8664] SQL0390N The function "MAXIMO.TEST3" resolved to specific function "SQL110418102057500" that is not valid in the context where it is used. SQLSTATE=42887

    I am using Toad Freeware for DB2 4.0.1

    Damn, i cant create Return Table type of a function

    I dont get any error when i create a single return type function.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First, I don't know Toad Freeware.
    So, if the error was related to Toad environment, I can't help you.

    When did you recieved the error SQL0390N?
    If it was the time you executed it, how did you invoked the function?

    What DB2 version(and platform OS) are you using?


    Here is an example of table function tested on Command Editor.
    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.2
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION test_emp()
    RETURNS TABLE(empid INTEGER)
    LANGUAGE SQL
    RETURN SELECT INT(empno) FROM employee WHERE workdept = 'E11'
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT *
      FROM TABLE( test_emp() );
    ------------------------------------------------------------------------------
    
    EMPID      
    -----------
            310
            280
             90
            300
            290
    
      5 record(s) selected.

  5. #5
    Join Date
    Apr 2011
    Posts
    3
    That worked! I dont know how or why, but now i can execute create function queries similar to your example. Thanks a lot,

    One more question,

    Is this the simplest way to return multiple values?

    Thanks

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is this the simplest way to return multiple values?
    I couldn't remember other ways than "RETURN SELECT ...." in a function.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is this the simplest way to return multiple values?
    I couldn't remember other ways than "RETURN SELECT ...." in a function.
    Honesty, I ignored some other syntax.

    RETURN VALUES ...
    RETURN (SELECT ...
    RETURN (VALUES ...

    Anyway, I think that you can't use cursor in an SQL TABLE function.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The difference discussed here is between a scalar function, which can be used anywhere in SQL statements where expressions are allowed, and table functions, which return a table. Naturally, you cannot use the result of a table function as expression because comparing such a table T in a predicate like "T > 'abc'" has no semantics. Thus, you can query that table and work with the rows by calling the function in the WHERE clause as Tonkuma has shown.

    Regarding the original question: if you always know how many INTs you want to return, you could also use a function that returns a single row with multiple (but a fixed number of) columns. However, that is still a table function since it does not return a single, scalar value.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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