Topic
  • 1 reply
  • Latest Post - ‏2012-10-16T11:44:34Z by SystemAdmin
SystemAdmin
SystemAdmin
1731 Posts

Pinned topic Create function returns ROW

‏2012-09-24T07:06:11Z |
Hi,

I would like to create a function that returns two or more values. I thought the function could return a tuple in the form of a ROW.

I checked the documentation for the "create function", and it says something about returning a ROW, but the example is not clear, and there is nothing more.

http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0003493.html

I would like something like this:


--#SET TERMINATOR @; create function f1 (a 

int) returns row (b int, c 

int) begin declare type f as row (d int, e 

int); set f.d = a; set f.e = a+a; 

return f; end@


This does not work, and I receive the SQL0628 error.

I have to declare the row as a type before the function:


--#SET TERMINATOR @; CREATE TYPE RO AS ROW (A INT, B INT)@   CREATE FUNCTION F1 (A INT) RETURNS RO BEGIN DECLARE ROA ro; SET ROA.A = A; SET ROA.B = A+A; RETURN ROA; END@


But, I do not want that, I want to use the ROW option in the create function.

I do not know how to return the row, because I always have syntax error.

Can someone provide me an example of a function, using row as output type?
I would appreciate your help if the example provided can be execute immediately (not like the example in the InfoCenter, PERSON?)
Updated on 2012-10-16T11:44:34Z at 2012-10-16T11:44:34Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1731 Posts

    Re: Create function returns ROW

    ‏2012-10-16T11:44:34Z  
    Hi there,

    Even when the documentation is not clear enough I think your only problem is that you need to create the function body as an inlined compound SQL statement, i.e. the next code works fine for me in 10.1:

    
    CREATE OR REPLACE FUNCTION F1 (A INTEGER) RETURNS ROW (B INTEGER, C INTEGER) LANGUAGE SQL BEGIN ATOMIC DECLARE D INTEGER; DECLARE E INTEGER; SET D=A; SET E=A+A; RETURN VALUES (D, E); END@
    


    LooW