Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    20

    Question Unanswered: Cursor support in functions

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Deepak,
    No, only in stored procedures.

    Andy

  3. #3
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Cursor support in functions

    You can use

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

    It acts as a CURSOR/LOOP combination.

    Originally posted by deepaka
    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

  4. #4
    Join Date
    Jan 2004
    Posts
    20
    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.

  5. #5
    Join Date
    Dec 2004
    Posts
    54
    can you please give example for this

  6. #6
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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));

  7. #7
    Join Date
    Dec 2004
    Posts
    54
    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

  8. #8
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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
    @

  9. #9
    Join Date
    Dec 2004
    Posts
    54
    Thanks a lot, its working fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •