Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Location
    Sydney
    Posts
    13

    Unanswered: Using a cursor in a UDF

    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 =(

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    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.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Double posted.

  4. #4
    Join Date
    Jul 2009
    Location
    Sydney
    Posts
    13
    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

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 11-18-09 at 23:59.

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Hi gamo,

    Here is one I had written for our DEV team.

    -----------------------------------------------------------------------------------------------------------
    -- 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

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)
    Last edited by tonkuma; 11-19-09 at 13:17.

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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
    Last edited by DBFinder; 11-19-09 at 09:56.

Posting Permissions

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