Results 1 to 10 of 10

Thread: UDF Error

  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: UDF Error

    I am new to DB2 but have a background in Oracle. I am trying to create a user defined function that will return one column from several rows and concatenate the values into one string. This seems pretty straight forward but I have been working on this for two days and can not get it to create. Here is my code:

    Code:
    CREATE FUNCTION NES_LIGHTING_BILLING( p_contract_no DECIMAL(7,0) )
    LANGUAGE SQL
    RETURNS VARCHAR(2048)
    --SET OPTION COMMIT = *NONE
    
    BEGIN
    DECLARE STRLIST VARCHAR(2048) DEFAULT ' ';
    
    FOR V AS C1 CURSOR
      FOR SELECT CASE OL_TYPE_ID
                    WHEN '1' THEN '175 MV' 
                    WHEN '2' THEN '250 MV' 
                    WHEN '3' THEN '400 MV' 
                    WHEN '4' THEN '100 HPS' 
                    WHEN '5' THEN '250 HPS' 
                    WHEN '6' THEN '400 HPS' 
                    WHEN '7' THEN '150 HPS' 
                    WHEN 'D' THEN '400 S FL' 
                    WHEN 'G' THEN '100 MH' 
                    WHEN 'H' THEN '250 MH' 
                    WHEN 'I' THEN '400 MH' 
                    WHEN 'J' THEN '1000 MH' 
                    WHEN 'L' THEN '30FT WOOD POLE' 
                    WHEN 'M' THEN '35FT WOOD POLE' 
                    WHEN 'N' THEN '40FT WOOD POLE' 
                    WHEN 'O' THEN '45FT WOOD POLE' 
                    WHEN 'P' THEN '70FT WOOD POLE' 
                    WHEN 'R' THEN '3OFT METAL POLE' 
                    WHEN 'V' THEN '70FT METAL POLE' 
                 END ||'('|| REPLACE(VARCHAR(OL_QTY), '.', '') ||','|| REPLACE(VARCHAR(OL_ACTV_QTY), '.', '') ||')' AS TYPE_QTY_ACT 
            FROM T14TOPS.CI086V 
           WHERE OL_CONT_NBR = p_contract_no;
       SET STRLIST = TRIM(STRLIST) CONCAT ' ' CONCAT V.TYPE_QTY_ACT;
      END FOR;
      
     RETURN LTRIM(STRLIST);
     END
    Any thing jump out at anyone? I appreciate the help.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How abou this?

    Example 1: may work on DB2 9.7 Fix Pack 4 for LUW or later.
    Code:
    CREATE FUNCTION NES_LIGHTING_BILLING( p_contract_no DECIMAL(7,0) )
    LANGUAGE SQL
    RETURNS VARCHAR(2048)
    NO EXTERNAL ACTION
    RETURN
    SELECT LISTAGG(  CASE OL_TYPE_ID
                     WHEN '1' THEN '175 MV' 
                     WHEN '2' THEN '250 MV' 
                     WHEN '3' THEN '400 MV' 
                     WHEN '4' THEN '100 HPS' 
                     WHEN '5' THEN '250 HPS' 
                     WHEN '6' THEN '400 HPS' 
                     WHEN '7' THEN '150 HPS' 
                     WHEN 'D' THEN '400 S FL' 
                     WHEN 'G' THEN '100 MH' 
                     WHEN 'H' THEN '250 MH' 
                     WHEN 'I' THEN '400 MH' 
                     WHEN 'J' THEN '1000 MH' 
                     WHEN 'L' THEN '30FT WOOD POLE' 
                     WHEN 'M' THEN '35FT WOOD POLE' 
                     WHEN 'N' THEN '40FT WOOD POLE' 
                     WHEN 'O' THEN '45FT WOOD POLE' 
                     WHEN 'P' THEN '70FT WOOD POLE' 
                     WHEN 'R' THEN '3OFT METAL POLE' 
                     WHEN 'V' THEN '70FT METAL POLE' 
                     ELSE          '*' || OL_TYPE_ID || '*'
                     END
                     || '(' || REPLACE( VARCHAR(OL_QTY)      , '.' , '' )
                     || ',' || REPLACE( VARCHAR(OL_ACTV_QTY) , '.' , '' )
                     || ')'
                   , ' '
                  )
     FROM  T14TOPS.CI086V 
     WHERE OL_CONT_NBR = p_contract_no
    ;
    Last edited by tonkuma; 04-17-12 at 09:55.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
                     || '(' || REPLACE( VARCHAR(OL_QTY)      , '.' , '' )
                     || ',' || REPLACE( VARCHAR(OL_ACTV_QTY) , '.' , '' )
    the expressions may be replaced by
    Code:
                     || '(' || STRIP( DIGITS(OL_QTY)      , L , '0' )
                     || ',' || STRIP( DIGITS(OL_ACTV_QTY) , L , '0' )

  4. #4
    Join Date
    Apr 2012
    Posts
    4

    Version is different

    Well, I was just able to find out that we are on DB2 version 9.1 runing on Z OS. I tried the example that was posted but our version doesn't include the LISTAGG function I guess. Thanks for trying.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    we are on DB2 version 9.1 runing on Z OS.
    Your FOR statement might have some syntax errors.

    Code:
    Syntax
    
    >>-+--------+--FOR--+-------------------+----------------------->
       '-label:-'       '-for-loop-name--AS-'   
    
    >--+--------------------------------------------+--------------->
       |                      .-WITHOUT HOLD-.      |   
       '-cursor-name--CURSOR--+--------------+--FOR-'   
                              '-WITH HOLD----'          
    
                             .----------------------------.   
                             V                            |   
    >--select-statement--DO----SQL-procedure-statement--;-+--------->
    
    >--END FOR--+-------+------------------------------------------><
                '-label-'
    DB2 9 - DB2 SQL - FOR statement


    and, if DEFAULT of STRLIST was zero length string, TRIM function might be not necessary, like
    Code:
    DECLARE STRLIST VARCHAR(2048) DEFAULT '';
    
    
    FOR /*V AS C1 CURSOR*/
      /*FOR*/ SELECT CASE OL_TYPE_ID
    ...
    ...
           WHERE OL_CONT_NBR = p_contract_no/*;*/
       DO SET STRLIST = /*TRIM(*/STRLIST/*)*/ CONCAT ' ' CONCAT V.TYPE_QTY_ACT;
      END FOR;
    Note: Italic-Red are not syntax error, but not neccesary.

  6. #6
    Join Date
    Apr 2012
    Posts
    4

    Thanks.

    I will take a look at the information you posted and see if I can see anything there. Thanks for your help.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    An DB2 z/OS, you could use the XMLAGG() and XMLSERIALIZE() with a couple REPLACE expressions around it to get rid of the XML tags: need a query... - DB2 Database Admittedly, this is not very nice, but doing procedural logic in SQL statements is not always the best idea and built-in aggregation functions may be better for you. Give it a try.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes Stolze!

    I forgot that LISTAGG can be replaced by "XMLAGG() ...".
    It might be an old times invention in my mind.

    Code:
           LISTAGG(
                     <expression>
                   , ' '
                  )
    in my Example 1 can be replaced by
    (for example)
    Code:
           SUBSTR(
              REPLACE(
                 REPLACE(
                    XMLSERIALIZE(
                       XMLAGG(
                          XMLELEMENT(
                             NAME "e"
                           , <expression>
                          )
                       )
                     AS CLOB
                    )
                  , '</e><e>' , ' '
                 )
               , '</e>' , ''
              )
            , 4
           )
    I still couldn't decided what was the best pretty formatting as(or for?) the position/indention of "AS ..." phrase.
    Last edited by tonkuma; 04-17-12 at 14:18. Reason: Add the sentence "I still couldn't decided what was the best pretty formatting ...". Move left "AS CLOB"

  9. #9
    Join Date
    Apr 2012
    Posts
    4

    Thanks All

    Thanks everyone. I will try that and see where I get to. I appreciate all of the help. I never knew that there were so many differences between Oracle and DB2 but I am enjoying the opportunity to learn!

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Thanks and welcome to come DB2 world.

    But, I think Olacle also supports LISTAGG function, at least in latest version.


    I am enjoying the opportunity to learn!
    I want to express my thoughts to agree with you from my heart,
    to learn something new is always fun/joy/pleasure for my life, even apart from computers.

Posting Permissions

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