Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Column value should have comma

    Hi;

    Please find the below query result set should have more than one value with comma separator if the same SUPP_code and MOD_NUM has different one more vlaue of SUPP_MA_CODE

    DB2 v9.1 Z/OS

    SUPP_MA_CODE VARCHAR(50)

    Code:
    WITH
    TABLE1
    ( SUPP_CODE , SUPP_MA_CODE , MOD_NUM ) AS (
    VALUES
     ('AAAA',X1111','A111692-SS')
    ,('AAAA','Y2222','A111692-SS')
    ,('AAAA','S1111','B222449-BB')
    ,('AAAA','S1111','C111621-CC')
    ,('BBBB','Z2222','D111692-DD')
    ,('BBBB','T2222','D111692-DD')
    ,('BBBB','W2222' 'D111692-DD')
    ,('CCCC','E1111','B222441-EE')
    ,('CCCC','R1114','C111621-FF')
    
    )
    
    SELECT DISTINCT                              
            T1.SUPP_CODE                  
          , T1.SUPP_MA_CODE            
          , T1.MOD_NUM                  
                             
      FROM TABLE1 T1
    EXPECTED RESULT SET
    Code:
    SUPP_CODE   SUPP_MA_CODE                  MOD_NUM
    AAAA          X1111,Y2222                A111692-SS
    AAAA          S1111                      B222449-BB
    AAAA          S1111                      C111621-CC
    BBBB          Z2222,T2222,W2222          D111692-DD
    CCCC          E1111                      B222441-EE
    CCCC          R1114                      C111621-FF
    PLEASE HELP

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to use XMLAGG to concatenete values.

    There are some samples in this forum.

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    I have rewritten query and included the XMLAGG function

    Code:
    WITH
    TABLE1
    ( SUPP_CODE , SUPP_MA_CODE , MOD_NUM ) AS (
    VALUES
     ('AAAA',X1111','A111692-SS')
    ,('AAAA','Y2222','A111692-SS')
    ,('AAAA','S1111','B222449-BB')
    ,('AAAA','S1111','C111621-CC')
    )
    
    SELECT DISTINCT                              
            T1.SUPP_CODE                  
       
    ,SUBSTR(
    XMLSERIALIZE(
    XMLAGG(
    XMLTEXT(T1.SUPP_MA_CODE || ',')
    
    ORDER BY T1.SUPP_CODE,T1.MOD_NUM
    )
    AS CLOB(1M))
    ,1,25) AS SUPP_MA_CODE1
    , T1.MOD_NUM        
    
      FROM TABLE1 T1 
    
    
    GROUP BY
    T1.SUPP_CODE
    , T1.MOD_NUM
    But Result set not returns correct rows


    Code:
    ERROR output
    
    SUPP_CODE   SUPP_MA_CODE1                  MOD_NUM
    AAAA          X1111,X1111                A111692-SS
    AAAA          S1111                      B222449-BB
    AAAA          S1111                      C111621-CC
    Expected Result set is
    Code:
    
    SUPP_CODE   SUPP_MA_CODE1                  MOD_NUM
    AAAA          X1111,Y2222                A111692-SS
    AAAA          S1111                      B222449-BB
    AAAA          S1111                      C111621-CC
    Please help..

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did your query really worked?
    Because, I thought you might forgot a quotation mark(') at fifth line, like
    ('AAAA','X1111','A111692-SS')

    If I added the quotation mark('),
    I got the error message.
    SQL0134N Improper use of a string column, host variable, constant, or
    function "SUPP_MA_CODE1". SQLSTATE=42907

    Then I removed DISTINCT, like...
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    TABLE1
    ( SUPP_CODE , SUPP_MA_CODE , MOD_NUM ) AS (
    VALUES
     ('AAAA','X1111','A111692-SS')
    ,('AAAA','Y2222','A111692-SS')
    ,('AAAA','S1111','B222449-BB')
    ,('AAAA','S1111','C111621-CC')
    )
    
    SELECT /*DISTINCT*/                              
            T1.SUPP_CODE                  
       
    ,SUBSTR(
    XMLSERIALIZE(
    XMLAGG(
    XMLTEXT(T1.SUPP_MA_CODE || ',')
    
    ORDER BY T1.SUPP_CODE,T1.MOD_NUM
    )
    AS CLOB(1M))
    ,1,25) AS SUPP_MA_CODE1
    , T1.MOD_NUM        
    
      FROM TABLE1 T1 
    
    
    GROUP BY
    T1.SUPP_CODE
    , T1.MOD_NUM;
    ------------------------------------------------------------------------------
    
    SUPP_CODE SUPP_MA_CODE1             MOD_NUM   
    --------- ------------------------- ----------
    AAAA      X1111,Y2222,              A111692-SS
    AAAA      S1111,                    B222449-BB
    AAAA      S1111,                    C111621-CC
    
      3 record(s) selected.
    Note: All my test were run on DB2 9.7.5 on Windows.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My previous query returned very near to your Expected Result set, except last comma in SUPP_MA_CODE1.

    I modified the query a little to return same result as your Expected Result set.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    TABLE1
    ( SUPP_CODE , SUPP_MA_CODE , MOD_NUM ) AS (
    VALUES
     ('AAAA','X1111','A111692-SS')
    ,('AAAA','Y2222','A111692-SS')
    ,('AAAA','S1111','B222449-BB')
    ,('AAAA','S1111','C111621-CC')
    )
    
    SELECT T1.SUPP_CODE                  
    ,SUBSTR(
    XMLSERIALIZE(
    XMLAGG(
    XMLTEXT(',' || T1.SUPP_MA_CODE)
     ORDER BY T1.SUPP_CODE,T1.MOD_NUM
    )
    AS CLOB(1M))
    ,2,25) AS SUPP_MA_CODE1
    , T1.MOD_NUM        
    
      FROM TABLE1 T1 
    
    GROUP BY
    T1.SUPP_CODE
    , T1.MOD_NUM;
    ------------------------------------------------------------------------------
    
    SUPP_CODE SUPP_MA_CODE1             MOD_NUM   
    --------- ------------------------- ----------
    AAAA      X1111,Y2222               A111692-SS
    AAAA      S1111                     B222449-BB
    AAAA      S1111                     C111621-CC
    
      3 record(s) selected.

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    I am receiving same ERROR result set..could please provide alternate query..

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see my last example...
    Code:
    ...
    SELECT T1.SUPP_CODE
    ,SUBSTR(
    XMLSERIALIZE(
    XMLAGG(
    XMLTEXT(',' || T1.SUPP_MA_CODE)
     ORDER BY T1.SUPP_CODE,T1.MOD_NUM
    )
    AS CLOB(1M))
    ,2,25) AS SUPP_MA_CODE1
    ...
    By the way,
    I thought there were two problems in your sample data in OP,
    Code:
    WITH
    TABLE1
    ( SUPP_CODE , SUPP_MA_CODE , MOD_NUM ) AS (
    VALUES
     ('AAAA','X1111','A111692-SS')
    ,('AAAA','Y2222','A111692-SS')
    ,('AAAA','S1111','B222449-BB')
    ,('AAAA','S1111','C111621-CC')
    ,('BBBB','Z2222','D111692-DD')
    ,('BBBB','T2222','D111692-DD')
    ,('BBBB','W2222','D111692-DD')
    ,('CCCC','E1111','B222441-EE')
    ,('CCCC','R1114','C111621-FF')
    
    )
    It was not included a quotation mark(already I pointed out) and a comma(,).

    I think that putting a blank before and after each separatior characters(e.g. comma, paretheses, so on...), make it easy to find syntax error.

    Like this example(problems were corrected)
    Code:
    WITH
    TABLE1
    ( SUPP_CODE , SUPP_MA_CODE , MOD_NUM ) AS (
    VALUES
      ( 'AAAA' , 'X1111' , 'A111692-SS' )
    , ( 'AAAA' , 'Y2222' , 'A111692-SS' )
    , ( 'AAAA' , 'S1111' , 'B222449-BB' )
    , ( 'AAAA' , 'S1111' , 'C111621-CC' )
    , ( 'BBBB' , 'Z2222' , 'D111692-DD' )
    , ( 'BBBB' , 'T2222' , 'D111692-DD' )
    , ( 'BBBB' , 'W2222' , 'D111692-DD' )
    , ( 'CCCC' , 'E1111' , 'B222441-EE' )
    , ( 'CCCC' , 'R1114' , 'C111621-FF' )
    )
    If your exact sample data in OP was written according to my opinion,
    it would looked like this, and I think you can see absence of a quoation mark and a comma.
    Code:
    WITH
    TABLE1
    ( SUPP_CODE , SUPP_MA_CODE , MOD_NUM ) AS (
    VALUES
      ( 'AAAA' , X1111' , 'A111692-SS' )
    , ( 'AAAA' , 'Y2222' , 'A111692-SS' )
    , ( 'AAAA' , 'S1111' , 'B222449-BB' )
    , ( 'AAAA' , 'S1111' , 'C111621-CC' )
    , ( 'BBBB' , 'Z2222' , 'D111692-DD' )
    , ( 'BBBB' , 'T2222' , 'D111692-DD' )
    , ( 'BBBB' , 'W2222'   'D111692-DD' )
    , ( 'CCCC' , 'E1111' , 'B222441-EE' )
    , ( 'CCCC' , 'R1114' , 'C111621-FF' )
    )

  8. #8
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    Usually i would not use CTE in the query..I used only the SELECT query what ever you suggest..So no need to care about WITH clauses.

    For your convinence only i made the query to CTE

    I incorporated your last suggestion also,but result set will be getting error like below

    Code:
    SUPP_CODE   SUPP_MA_CODE1                              MOD_NUM
    AAAA          X1111,X1111,X1111,X1111,X                A111692-SS
    AAAA          S1111                                              B222449-BB
    AAAA          S1111                                              C111621-CC
    Here is the query
    Code:
    SELECT                               
            T1.SUPP_CODE                  
       
    ,SUBSTR(
    XMLSERIALIZE(
    XMLAGG(
    XMLTEXT(',' || T1.SUPP_MA_CODE)
    
    ORDER BY T1.SUPP_CODE,T1.MOD_NUM
    )
    AS CLOB(1M))
    ,2,25) AS SUPP_MA_CODE1
    , T1.MOD_NUM        
    
      FROM TABLE1 T1
    Thanks for the understanding

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    For your convinence only i made the query to CTE
    Thank you very much for your considerations.

    You need not to try them, because lack of access to z/OS is my problem.

    So, please publish your test data and executed query exactly
    by CREATE TABLE statenment, INSERT statement, executed query and the result you received,
    to share your issue with me as much as possible.

    Then, I'll try to recreare your issue on my DB2 for Windows.
    Most of the statements on DB2 for z/OS would be able to execute on DB2 for LUW without modifications.
    If some modifications were necessary, I'll try the modifications.

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I've modified Tonkuma's query slightly, so that it works on DB2 for z/OS (version 9 or 10):
    Code:
    WITH TABLE1 ( SUPP_CODE , SUPP_MA_CODE , MOD_NUM ) AS
    ( SELECT 'AAAA','X1111','A111692-SS' FROM sysibm.sysdummy1
      UNION ALL
      SELECT 'AAAA','Y2222','A111692-SS' FROM sysibm.sysdummy1
      UNION ALL
      SELECT 'AAAA','S1111','B222449-BB' FROM sysibm.sysdummy1
      UNION ALL
      SELECT 'AAAA','S1111','C111621-CC' FROM sysibm.sysdummy1
    )
    
    SELECT T1.SUPP_CODE,
           SUBSTR(
             XMLSERIALIZE(
               XMLAGG(
                 XMLTEXT(',' || T1.SUPP_MA_CODE)
                 ORDER BY T1.SUPP_CODE,T1.MOD_NUM)
               AS CLOB(1M)),
             2) AS SUPP_MA_CODE1,
           T1.MOD_NUM
      FROM TABLE1 T1
    GROUP BY T1.SUPP_CODE, T1.MOD_NUM;
    Note that I have also removed the ",25" from the substr argument list -- I suspect this was the main cause for Billa007's failing query on (most likely) longer input data.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Sep 2011
    Posts
    220
    Thanks tonkuma and Peter...working fine ...

  12. #12
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    I was using the above query with use of UNION function for another query..it retuns the below error

    "SQL0134N Improper use of a string column, host variable, constant, or
    function "". SQLSTATE=42907 "

    my understanding is "we should not use CLOB while the query has UNION "

    Is it correct..Can you please provide alternate way..

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see the manual.
    DB2 9 - DB2 SQL - fullselect

    UNION DISTINCT or UNION ALL

    If UNION ALL is specified, the result consists of all rows in R1 and R2. With UNION DISTINCT, the result is the set of all rows in either R1 or R2 with the redundant duplicate rows eliminated. In either case, each row of the result table of the union is either a row from R1 or a row from R2.
    Rules for columns:

    ...
    •R1 and R2 must not include columns having a data type of CLOB, BLOB, DBCLOB, XML, or a distinct type that is based on any of these types. However, this rule is not applicable when UNION ALL is used with the set operator.
    ...
    Last edited by tonkuma; 06-13-12 at 06:19. Reason: Add first quote about "UNION DISTINCT or UNION ALL"

  14. #14
    Join Date
    Sep 2011
    Posts
    220
    For using of CLOB,can we use CHAR or VARCHAR instead of CLOB ?
    SUPP_CODE char(5)
    SUPP_MA_CODE VARCHAR(50)
    MOD_NUM char(40)

    any suggestions?

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Read the manual and try to uderstand differences of these datatypes.
    DB2 9 - DB2 SQL - Character strings
    DB2 9 - DB2 SQL - Fixed-length character strings
    DB2 9 - DB2 SQL - Varying-length character strings
    DB2 9 - DB2 SQL - Large objects (LOBs)

    And, reconsider your requirements.
    Then you would be able to know how to do, by yourself.

Posting Permissions

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