Using a cursor in a UDF

gamo · #1 (**permalink**) 11-18-09, 01:35

Hi,

I am trying to concatenate an arbitrary # of results in a UDF and return a single comma delimited field

I realise that this isn't a total solution as I'm not guaranteed that an unspecified # of results will fit in the return var, but say that's not an issue for me at the moment

Code:

  CREATE FUNCTION GETCOMMENTS( session integer, cons varchar( 1 ), start varchar(1) ) returns varchar( 1024 ) 
   DETERMINISTIC NO EXTERNAL ACTION 
   BEGIN ATOMIC 
   DECLARE comments varchar( 10 );
   SET comments = '';
   FOR EACH_RECORD AS C1 CURSOR FOR 
		SELECT com FROM QA where ID = start
   		DO SET comments = comments || '\n' || currcomment ;
   	END FOR; 
   CLOSE temp;
   RETURN comments; 
   END;

I am getting the error:

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 "C1 CURSOR FOR" was found following "FOR EACH_RECORD AS". Expected tokens may include: "<space>". LINE NUMBER=1. SQLSTATE=42601

I'm sorry - I'm pretty unfamiliar with cursors and need some help to work this out =(

tonkuma · #2 (**permalink**) 11-18-09, 03:06

You can't use cursor in an UDF.
You can use Compound SQL (inlined) in an SQL function.
Compound SQL (inlined) include FOR statement.
But, there is a note to "FOR ... CURSOR ..." in DB2 SQL Reference Volume 2.

Quote:

Notes:
1 This option can only be used in the context of an SQL procedure or a compound SQL (compiled) statement in an SQL procedure.

I think you have two choices.
1) Make return value long enough to include your practical data.
2) TABLE UDF.

tonkuma · #3 (**permalink**) 11-18-09, 03:14

Double posted.

gamo · #4 (**permalink**) 11-18-09, 17:45

I'm sorry - I am a bit of a newbie here

could you give me an example?

here is a bit more description of what I want

userid | value
123 a
123 b
123 c
345 d
345 e

GETCOMMENTS( 123 ) = 'abc'
GETCOMMENTS( 345 ) = 'de'

does that make more sense? I thought cursors would be the logical solution to this, as I could iterate through as many rows as are returned, and I don't know what to do otherwise

tonkuma · #5 (**permalink**) 11-18-09, 20:02

Here is an example.
It is using XMLAGG to concatenate values of rows.

Another way is using recursive query.

You would find more examples by searching with keyword "concatenate strings" or "concatenate values" in this forum.

CREATE TABLE and populate it:

Code:

------------------------------ Commands Entered ------------------------------
CREATE TABLE QA
(userid INTEGER
,value  VARCHAR(7)
);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO QA
VALUES
 (123, 'a')
,(123, 'b')
,(123, 'c')
,(345, 'd')
,(345, 'e');
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

CREATE FUNCTION:

Code:

------------------------------ Commands Entered ------------------------------
CREATE FUNCTION GetComments(p_userid INTEGER )
 RETURNS varchar( 1024 ) 
 DETERMINISTIC
 NO EXTERNAL ACTION 
RETURN
SELECT SUBSTR(
       XMLSERIALIZE(
          XMLAGG(
             XMLTEXT(',' || value)
             ORDER BY value
          )
          AS VARCHAR(1024)
       )
       , 2)
  FROM qa
 WHERE userid = p_userid
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

Use of the function:

Code:

------------------------------ Commands Entered ------------------------------
SELECT userid
     , VARCHAR( getcomments( userid ), 50) AS comments
  FROM (VALUES 123, 345 ) q(userid)
;
------------------------------------------------------------------------------

USERID      COMMENTS                                          
----------- --------------------------------------------------
        123 a,b,c                                             
        345 d,e                                               

  2 record(s) selected.

DBFinder · #6 (**permalink**) 11-19-09, 08:00

Hi gamo,

Here is one I had written for our DEV team.

Quote:

-----------------------------------------------------------------------------------------------------------
-- This UDF takes in promo_id and returns CasinoList
-----------------------------------------------------------------------------------------------------------

CREATE FUNCTION DB2ADMIN.GetCasinoList(promoid Varchar(13))
RETURNS varchar(20)
SPECIFIC GetCasinoList
LANGUAGE SQL
BEGIN ATOMIC

DECLARE Casinolist varchar(20) default '';

For cur as SELECT casino_id FROM promo_casino WHERE promo_id =promoid
do

Set Casinolist = CasinoList CONCAT cur.casino_id;

END for;

RETURN VALUES (Casinolist);
END
@

Regards
DBFinder

tonkuma · #7 (**permalink**) 11-19-09, 08:46

I'm sorry! I made mistake.

You can use FOR statement in UDF.

The note 1 which I mentioned was for the optional clause in the following FOR syntax.
Syntax of FOR statement:
[label:] FOR for-loop-name AS [cursor-name [WITHOUT HOLD | WITH HOLD] CURSOR FOR (1)] select-statement DO SQL-routine-statement END FOR [label]

gamo,
Your UDF will work by removing "C1 CURSOR FOR". (with minor correction)

DBFinder · #8 (**permalink**) 11-19-09, 08:50

Much appreciated

I had exactly same hurdle as gamo. Your comments were right !? We are using this UDF in Production.

Actually for-loop-name is already a cursor (special type) which opens and closes automatically. That's why we cannot specify its name clause.

DBFinder

Internet Services

Web Development

Digital Marketing

Consumer Tech