Hi All,

Do db2 user defined functions support cursors. I tried using a cursor in one of the functions and it gave me an error.

Thanks
Deepak

Deepak,
No, only in stored procedures.

Andy

You can use

FOR c1 AS
SELECT ......
DO
some processing
END FOR;

It acts as a CURSOR/LOOP combination.

Thanks Guys,

I tried
FOR c1 AS
SELECT ......
DO
some processing
END FOR;

and it compiled. Will try to do processing and hopefully it will work too.

can you please give example for this

Here is code that would be between the BEGIN..END for function body. It concatenates a list of codes together.

DECLARE v_sic_cde VARCHAR(40) DEFAULT ' ';

FOR sic AS
SELECT sic_cde
FROM sic_t
WHERE cust_id = p_cust_id <-- p_ passed into function
AND duns_sic_cde_i = 'Y'
AND row_exp_dtm IS NULL
ORDER BY sic_cde_seqid
DO
SET v_sic_cde = v_sic_cde || sic.sic_cde || ', ';

END FOR;

RETURN LTRIM(SUBSTR(v_sic_cde, 1, LENGTH(v_sic_cde) - 2));

It is returning empty string

here is the code:

CREATE FUNCTION test_fn()

RETURNS varchar(500)
LANGUAGE SQL
READS SQL DATA
EXTERNAL ACTION
NOT DETERMINISTIC

BEGIN ATOMIC

declare final VARCHAR(500) default '';--
declare varia VARCHAR(10) default '';--

FOR varia AS SELECT user_id FROM cmd where cmd_id = 1 DO
SET final = final || varia || ', ';
END FOR;
RETURN rtrim(final) ;
END
@

Actually i need to return user_ids separed by comma as a single string.

Please can you help me

Just changed a couple of things. Try now.

CREATE FUNCTION test_fn()

RETURNS varchar(500)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
NOT DETERMINISTIC

BEGIN ATOMIC

declare final VARCHAR(500) default '';

FOR varia AS SELECT user_id FROM cmd where cmd_id = 1 DO
SET final = final || varia.user_id || ', ';
END FOR;
RETURN LTRIM(SUBSTR(final, 1, LENGTH(final) - 2)); <-- strips the last ','
END
@

Thanks a lot, its working fine.