Want to return multiple integers from a table

crozwise · #1 (**permalink**) 04-18-11, 07:31

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.

tonkuma · #2 (**permalink**) 04-18-11, 08:05

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

crozwise · #3 (**permalink**) 04-18-11, 08:24

Quote:

Originally Posted by tonkuma

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.

tonkuma · #4 (**permalink**) 04-18-11, 08:43

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.

crozwise · #5 (**permalink**) 04-18-11, 08:57

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

tonkuma · #6 (**permalink**) 04-18-11, 09:03

Quote:

Is this the simplest way to return multiple values?

I couldn't remember other ways than "RETURN SELECT ...." in a function.

tonkuma · #7 (**permalink**) 04-18-11, 10:03

Quote:

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.

stolze · #8 (**permalink**) 04-19-11, 05:20

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech