Results 1 to 8 of 8

Thread: UDF - help

  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Unanswered: UDF - help

    I am doing some playing with creating and using a UDF and could use some assistance.

    Platform = LUW and Z/OS
    version >= 9.7 and z/os >= v10

    I am changing rows of accessories into a list of accessories. Have built a function table, that I can query and it returns the list I want.
    Code:
    CREATE FUNCTION db2dba.dave_GET_OPTINAL_ACC_LIST( IN_PO CHAR(10))
         RETURNS  table (ACC_CDE varchar(4000))
         LANGUAGE SQL
         NOT DETERMINISTIC
         READS SQL DATA 
         RETURN
    
    	WITH SRC( N, ACC_CDE ) AS
    	 (SELECT  ROW_NUMBER () OVER() AS NUM_ACC ,  
       			A2.CDE_ACSRY_TYPE || '-'
       			   || A2.CDE_ACCESSORY  as ACC_CDE
    	      FROM DB2DBA.VEH_VACS AS A2 
    	     WHERE  A2.NUM_PORD = in_po
    	       AND A2.CDE_STD_ACSRY IN( 'O') )
    	,REC( RN, ACC_CDE ) AS
    	(SELECT 2, CAST(ACC_CDE AS VARCHAR(2000)) as acc_cde
    	    FROM SRC WHERE N = 1
    	 UNION ALL
    	 SELECT N+1, RTRIM(REC.ACC_CDE) CONCAT ', ' 
    	        CONCAT RTRIM(SRC.ACC_CDE) as acc_cde
    	    FROM SRC, REC
    	    WHERE  N = RN) 
    		SELECT ACC_CDE FROM REC;
    here's the query:
    Code:
    select acc_cde from table(db2dba.dave_GET_OPTINAL_ACC_LIST('0471407360')) as t
    order by 1 desc fetch first 1 row only;
    and the resultset:
    Code:
     9-P01, 9-167, 9-320
    Works great.
    Now the problem I am having. In the above example I hardcoded the in_po that I wanted the resultset for. I want to be able to plug this function into a query and use a column as the input. Having no luck with that.

    Code:
    select ve.num_pord , 
    		(select acc_cde from table(db2dba.dave_GET_OPTINAL_ACC_LIST(ve.num_pord)) as t
    		order by 1 desc fetch first 1 row only) as acc
    from db2dba.veh_vehi  as ve
    where num_pord = '0471407360'
    result:
    Code:
     0471407360 NULL
    As you can see its the same in_po that I had put into function as a literal, but now I get a null returned. Any suggestions appreciated.

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    Hi,
    I think maybe you can use listagg aggregate function.
    select ve.num_pord
    ,listagg ( CDE_ACSRY_TYPE||'-'||CDE_ACCESSORY,', ' )
    from db2dba.veh_vehi
    where num_pord = '0471407360' and CDE_STD_ACSRY IN( 'O')
    group by num_pord

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The listagg does not work on Z/OS V10 though. Need a solution that works on all platforms, thus the UDF above. Have been trying a few other solutions and still no luck. Such as:
    Code:
    select ve.num_pord , t.BOB
    from db2dba.veh_vehi as ve
    left join
    table(db2dba.dave_GET_OPTINAL_ACC_LIST(ve.num_pord)) as t(BOB)
    on 1=1
    where num_pord = '0471407360';

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Won't XMLAGG work in both?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Yes, it does. In fact I am working on that now, not as straight forward as I would like to see it though. I am attempting the following:
    Code:
    SELECT ve.num_pord
    ,replace(replace(replace(cast(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME "x", A2.CDE_ACSRY_TYPE) order by A2.CDE_ACSRY_TYPE) AS CLOB(4000)) as varchar(4000)), '</x><x>', ',') , '<x>', '') , '</x>', '') as acc_cde
    FROM db2dba.veh_vehi as ve
    inner join db2dba.VEH_VACS AS A2 
        on ve.num_pord = A2.NUM_PORD
    where ve.num_pord = '0471407360'
    group by ve.num_pord
    Not quite the results I was wanting though:
    Code:
     0471407360
    0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,6,9,9,9
    Where I am expecting:
    Code:
    0471407360
     9-P01, 9-167, 9-320

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Ok I have it now.
    Resultset:
    Code:
     0471407360    |    9-P01   ,9-167   ,9-320
    Query:
    Code:
    SELECT ve.num_pord
    ,replace(replace(replace(cast(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME "x", A2.CDE_ACSRY_TYPE || '-'
       			   || A2.CDE_ACCESSORY) order by A2.CDE_ACSRY_TYPE || '-'
       			   || A2.CDE_ACCESSORY) AS CLOB(4000)) as varchar(4000)), '</x><x>', ',') , '<x>', '') , '</x>', '') as acc_cde
    FROM db2dba.veh_vehi as ve
    inner join db2dba.VEH_VACS AS A2 
        on ve.num_pord = A2.NUM_PORD
       AND A2.CDE_STD_ACSRY IN( 'O')   
    where ve.num_pord = '0471407360'
    group by ve.num_pord

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It might be a little simplified by using XMLTEXT instead of XMLELEMENT.

    Example 1:
    Code:
    SELECT ve.num_pord
         , SUBSTR(
              XMLSERIALIZE(
                 XMLAGG( XMLTEXT(', ' || CDE_ACSRY_TYPE || '-' || CDE_ACCESSORY) )
                 AS CLOB(4000) )
            , 3 ) AS acc_cde
     FROM  db2dba.veh_vehi AS ve
     INNER JOIN
           db2dba.veh_vacs AS A2 
      ON   A2.num_pord = ve.num_pord
       AND A2.CDE_STD_ACSRY IN( 'O')   
     WHERE ve.num_pord = '0471407360'
     GROUP BY
           ve.num_pord

    By the way,
    I had a question why you joined db2dba.veh_vehi?
    The following Example 2 might be equivalent to Example 1.

    Example 2:
    Code:
    SELECT A2.num_pord
         , SUBSTR(
              XMLSERIALIZE(
                 XMLAGG( XMLTEXT(', ' || CDE_ACSRY_TYPE || '-' || CDE_ACCESSORY) )
                 AS CLOB(4000) )
            , 3 ) AS acc_cde
     FROM  db2dba.veh_vacs AS A2 
     WHERE A2.num_pord = '0471407360'
       AND A2.CDE_STD_ACSRY IN( 'O')   
     GROUP BY
           A2.num_pord

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Tonkuma,
    Thanks for that suggestion on the XMLTEST, works great.

    As for your question with example 2. I just used that as a quick example. In reality that table will be joined with another and there will be quite a few predicates in the where clause to filter down to the NUM_PORD we want as we will not know that value coming into this query. That is why I needed my initial function to accept the column name rather than literal value that I was using for my test.

Posting Permissions

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