Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Unanswered: Problems in creating a DB2 UDF .. Please help

    Hai,

    I was trying to create a user defined function in DB2. I was not able to create the function and was getting few errors. Please find the code and the error message.



    CREATE FUNCTION DESC_FUNC(v_rpt_id integer, v_desc_typ_cde varchar(1))
    RETURNS VARCHAR(32672)
    DECLARE v_desc VARCHAR(32672) default '';
    for1: BEGIN ATOMIC
    FOR tmp_row AS
    {
    SELECT
    desc_seq_num, evnt_desc_txt
    FROM
    "t_flt_sfty_desc DES","t_flt_sfty_rpt rptin1"
    WHERE DES.EVNT_DTE=rptin1.EVNT_DTE
    AND DES.EVNT_SEQ_NUM=rptin1.EVNT_SEQ_NUM
    AND DES.RPT_EVNT_ORD_NUM = rptin1.RPT_EVNT_ORD_NUM
    AND DES.RPTEE_TYP_CDE = rptin1.RPTEE_TYP_CDE
    AND DES.View_typ_cde = 'D'
    AND desc_typ_cde = v_desc_typ_cde
    AND rpt_id= v_rpt_id
    ORDER BY desc_seq_num
    )
    }
    DO
    IF desc_seq_num =1 THEN
    SET v_desc = tmp_row.evnt_desc_txt;
    ELSE
    SET v_desc = v_desc ||tmp_row.evnt_desc_txt;
    END IF;
    END FOR for1;
    return v_desc;
    END


    I got the following error message

    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 "V_DESC" was found following "". Expected
    tokens may include: "ON AFTER <INTEGER>". SQLSTATE=42601



    Can any one please solve this.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Declare your variable inside the BEGIN block, not outside.

    Andy

  3. #3
    Join Date
    Apr 2010
    Posts
    3
    Thanks Andy.

    I tried executing the query like below.

    CREATE FUNCTION DESC_FUNC(v_rpt_id integer, v_desc_typ_cde varchar(1))
    RETURNS VARCHAR(32672)

    for1: BEGIN ATOMIC
    DECLARE v_desc VARCHAR(32672) default '';
    FOR tmp_row AS
    {
    SELECT
    desc_seq_num,varchar(evnt_desc_txt) evnt_desc_txt
    FROM
    db2fsr.t_flt_sfty_desc DES,db2fsr.t_flt_sfty_rpt rptin1
    WHERE DES.EVNT_DTE=rptin1.EVNT_DTE
    AND DES.EVNT_SEQ_NUM=rptin1.EVNT_SEQ_NUM
    AND DES.RPT_EVNT_ORD_NUM = rptin1.RPT_EVNT_ORD_NUM
    AND DES.RPTEE_TYP_CDE = rptin1.RPTEE_TYP_CDE
    AND DES.View_typ_cde = 'D'
    AND desc_typ_cde = 'D'
    AND rpt_id= 11136
    ORDER BY
    desc_seq_num
    )
    }
    DO
    IF desc_seq_num =1 THEN
    SET v_desc = tmp_row.evnt_desc_txt;
    ELSE
    SET v_desc = v_desc ||tmp_row.evnt_desc_txt;
    END IF;
    END FOR for1;
    return v_desc;
    END


    Iam getting the following error again

    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 ":" was found following "". Expected tokens may
    include: "ON AFTER <INTEGER>". SQLSTATE=42601
    Last edited by r2k1984; 04-06-10 at 10:10.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your syntax is wrong. Use the manuals for the correct syntax. What are the squiggle brackets for?

    Andy

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yet another approach is to search for "string concatenation". Because what you try to do is a simple recursive query or you could use the XMLAGG function.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The function body may be written by a RETURN statement, like this...
    (DB2 for LUW 9.5 or later)
    Code:
    CREATE FUNCTION DESC_FUNC(v_rpt_id integer, v_desc_typ_cde varchar(1) ) 
    RETURNS VARCHAR(32672) 
    RETURN
    SELECT XMLCAST(
             XMLGROUP( evnt_desc_txt
                       ORDER BY desc_seq_num
                     )
             AS VARCHAR(32672)
           )
      FROM t_flt_sfty_desc DES
         , t_flt_sfty_rpt  rptin1 
     WHERE DES.EVNT_DTE         = rptin1.EVNT_DTE 
       AND DES.EVNT_SEQ_NUM     = rptin1.EVNT_SEQ_NUM 
       AND DES.RPT_EVNT_ORD_NUM = rptin1.RPT_EVNT_ORD_NUM 
       AND DES.RPTEE_TYP_CDE    = rptin1.RPTEE_TYP_CDE 
       AND DES.View_typ_cde     = 'D' 
       AND desc_typ_cde         = v_desc_typ_cde 
       AND rpt_id               = v_rpt_id 
    ;
    If you are using DB2 for LUW 9.1 or earlier,
    you can use XMLSERIALIZE and XMLAGG instead of XMLCAST and XMLGROUP.

  7. #7
    Join Date
    Apr 2010
    Posts
    3
    Thanks Guys for the help. But nothing above worked for me.

    I tried executing my first query without ). Iam getting the same error.

    Iam using db2 8 so XMLCAst will not work ..I tried executing the above query but i was getting the below error.

    B21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returnedQL0104N An unexpected token "(" was found following "". Expected tokens may include: "ON AFTER <INTEGER>". SQLSTATE=42601

    SQL0104N An unexpected token "(" was found following "". Expected tokens may include: "ON AFTER <INTEGER>

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You'll find some working examples here: How to concatenate rows together? | db2ude (I searched for "DB2 string concatenation recursion")
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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