MVSFORUMS.com

jayi

Is it possible to open a Cursor inside a Scalar UDF?Attempts to do the same are giving errors.However the same code when compiled as a procedure(with the return variable replaced with an OUT parameter) is compiling fine.Please help!

Joy · Posted: Tue Jun 28, 2005 1:46 am Post subject:

Hi Jayi,
Even I'm trying to do the same thing but its not working.The IBM DB2 Red Book contains code that has a cursor declaration within a UDF.But its not working for me

rublu · Beginner Joined: 14 Jun 2005 Posts: 5 Topics: 2

Could you give the error messages you are getting. Actually it is possible to open the cursor inside an UDF (procedures).
_________________
------------
Ruben

jayi · Posted: Wed Jun 29, 2005 12:32 am Post subject:

Hi Rublu,

Here's the error code:DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "FOR" was found following " DECLARE C1
CURSOR". Expected tokens may include:
"<SQL_variable_condition_declaration>". LINE NUMBER=27. SQLSTATE=42601
sqlcode : -104

rublu · Beginner Joined: 14 Jun 2005 Posts: 5 Topics: 2

Jayi,

I misunderstood the context earlier. Scalar UDFs will not return any resultsets. Refer http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0009701.htm for more details.
_________________
------------
Ruben

jayi · Posted: Wed Jun 29, 2005 9:10 am Post subject:

Rublu,
My intention was not to return a result set.I was trying to perform an operation on each row of a table and form a concatenated string containing the values of only those rows that yielded a positive result to the processing.So I would have returned a string not a result set.Moreover I have tried opening cursors within Table Functions.Such attempts are throwing the same error