Results 1 to 8 of 8
  1. #1
    Join Date
    May 2013
    Posts
    3

    Unanswered: Calculating Counts related Scenario

    Hi

    I am new to SQL and have a question on a Counts scenario, I have represented the data sample and output format required in a Image.

    There will be duplicate rows in the data, I am looking for a way to find the count based on the combinations of error code on idkey as represented in the image.

    I run the queries as part of a Test. So, I would be manually running the SQL's occasionally.

    If you are not sure on the Query, Please do let me know.. what you think the approach should be?

    Thanks,
    Satya
    Attached Thumbnails Attached Thumbnails Prob.gif  

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please publish
    (1) Your DB2 version/release/fixpack and platform OS.
    (2) CREATE TABLE statement(s).
    (3) INSERT statement(s) to populate the table(s) created by (2).
    (4) Expected results by text(not by image).

    And, please use consistent names
    (e.g. you used idkey and id_key, fieldname and field)


    Anyway, try something like...
    Code:
    /* Not tested */
    SELECT COUNT(*) AS "Count of id_key"
         , VARCHAR(
              errorcodes
            , 50
           )        AS "Error Code(Combinations)"
     FROM  (SELECT LISTAGG(errorcode , ', ')
                      WITHIN GROUP(ORDER BY errorcode) AS errorcodes
             FROM  (SELECT DISTINCT
                           id_key
                         , errorcode
                     FROM  sample_test_data
                     WHERE objects   = 'obj1'
                       AND fieldname = 'a1'
                   )
             GROUP BY
                   id_key
           )
     GROUP BY
           errorcodes
    ;
    Last edited by tonkuma; 05-16-13 at 23:42. Reason: Add "WITHIN GROUP(ORDER BY errorcode)". Remove id_key from select list of second level subquery.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you provided more informations including,
    (1) Your DB2 version/release/fixpack and platform OS.
    (2) CREATE TABLE statement(s).
    (3) INSERT statement(s) to populate the table(s) created by (2).
    (4) Expected results by text(not by image).
    I might be able to test my query and to debug it.

  4. #4
    Join Date
    May 2013
    Posts
    3
    Thanks a ton.. tonkuma!! Sorry for the mistakes.

    I dont have any of the info as of now. I will have to contact other teams to find out the info.. I will try to get it and reply here.. appreciate the response!!

    I ran the query in WINSQL and got the below response

    Error: SQL0104N An unexpected token "WITHIN" was found following "WITHIN". Expected tokens may include: ", FROM INTO". SQLSTATE=42601
    (State:42601, Native Code: FFFFFF98)

    required output is the count of idkey with combinations of error key. I am good with any names as long as the logic works good .. Thanks again!!

    Count of idkey Error Code - Combinatons
    1 10 , 11
    1 10
    1 11
    Last edited by satya1046; 05-17-13 at 19:08.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    LISTAGG was supported from DB2 9.7.?(I can't remember fixpack) on LUW.

    You may want to use XMLAGG or XMLGROUP instead of LISTAGG.
    Recursive common table expression may be another option.

    Please search by XMLAGG or XMLGROUP in this forum, to look for the examples of usage of these functions.

  6. #6
    Join Date
    May 2013
    Posts
    3
    I tried LISTAGG, and got to know that the present platform does not support this function.

    1) DB2 Version : V10
    Platform : z/OS
    2&3) I could not get the create and insert queries, but a flat file is processed by informatica to load into the tables.

    Got any other suggestions for me ?
    Last edited by satya1046; 05-30-13 at 13:55.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    LISTAGG was supported from DB2 9.7.?(I can't remember fixpack) on LUW.

    You may want to use XMLAGG or XMLGROUP instead of LISTAGG.
    Recursive common table expression may be another option.

    Please search by XMLAGG or XMLGROUP in this forum, to look for the examples of usage of these functions.
    XMLAGG may be a substitute for LISTAGG on DB2 10 for z/OS.
    (Some additional expressions may be neccesary to format the result.)

    Please see Information Center for more details and samples.
    DB2 10 - DB2 SQL - XMLAGG

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example using XMLAGG.

    Code:
    SELECT COUNT(*) AS "Count of id_key"
         , VARCHAR(errorcodes , 50) AS "Error Code(Combinations)"
     FROM  (SELECT SUBSTR(
                      REPLACE( REPLACE(
                         XMLSERIALIZE(
                            XMLAGG(
                               XMLELEMENT(NAME "e" , errorcode)
                               ORDER BY errorcode
                            )
                          AS VARCHAR(100)
                         )
                       , '</e><e>' , ' , ' ) , '</e>' , ''
                      )
                    , 4
                   ) AS errorcodes
             FROM  (SELECT DISTINCT
                           id_key
                         , errorcode
                     FROM  sample_test_data
                     WHERE objects   = 'obj1'
                       AND fieldname = 'a1'
                   )
             GROUP BY
                   id_key
           )
     GROUP BY
           errorcodes
    ;

Posting Permissions

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