Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    5

    Unanswered: converting rows into columns with counting distinct IDs

    Hello everyone,
    Last time I had some exotic needs and I've got help here. Now I have some more exotic and I don't know how to solve it. I need help again.

    I use z/OS, DB2, PL/I SQL.

    I have table with several columns but I need only 3:

    GROUP - only 4 values are possible: 1,2,3,4
    SPOL - only 2 values are possible: 1,2
    OB - can be any value

    The same OB can have different GROUP but always the same SPOL.

    data

    Code:
    GROUP  SPOL      OB
    -------------------
        1	  1	100
        2     1     100
        3     1     100
        2     1     200
        1     2     300
        1     2     300
        4     1     400
        2     1     400
        1     1     400
        2     1     400
        1     1     400
        2     2     500
    I need to count distinct OBs grouping by GROUP and SPOL in the next combinations of GROUP and SPOL, and get it in one row
    Code:
    1,1  1,2  2,1  2,2  3,1  3,2  4,1  4,2
    so the result I need to get is:
    Code:
    2  1  3  1  1  0  1  0
    I tried with this code and it works fine in SPUFI but when I write it in PL/I program, it throws message
    Code:
    DSNH104I DSNHPARS LINE 261 COL 371  ILLEGAL SYMBOL   
    "R". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:           
    <END-OF-STATEMENT> QUERYNO HAVING WHERE GROUP ORDER  
    INTERSECT
    Code:
    WITH ORIGINAL_TABLE(GR, SPOL, OB) AS (                       
       SELECT GR, SPOL, OB                                       
       FROM SESSION.BENST                                        
       ),                                                        
    R(GR, SPOL, BR) AS (                                         
       SELECT GR, SPOL, COUNT(DISTINCT(OB))                      
       FROM  ORIGINAL_TABLE                                      
       GROUP BY GR, SPOL                                         
       )                                                         
       SELECT A11.BR, A12.BR, A21.BR, A22.BR, A31.BR, A32.BR, A41.BR, A42.BR
       FROM  R A11, R A12, R A21, R A22, R A31, R A32, R A41, R A42
       WHERE A11.GR=1 AND A11.SPOL='1' AND                         
             A12.GR=1 AND A12.SPOL='2' AND                         
             A21.GR=2 AND A21.SPOL='1' AND                         
             A22.GR=2 AND A22.SPOL='2' AND                         
             A31.GR=3 AND A31.SPOL='1' AND                         
             A32.GR=3 AND A32.SPOL='2' AND                         
             A41.GR=4 AND A41.SPOL='1' AND                         
             A42.GR=4 AND A42.SPOL='2'
    I'm using temporary table declared in program. All other inserts, deletengs and readings wit that table work fine.

    I know I wrote it like the Neanderthal man, sorry, woman, but I don't know better If you know some easier way which would work in PL/I no one happier than me

    Any help is appreciated.

    P.S. I apologize for my bad English, but I hope you understand what I want.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although, I'm not so familiar with DB2 for z/OS,
    I thought two possiblities.
    (1) A semicolon(;) might be necessary at the end of a statement.

    (2) The syntax "WITH common-table-expression" was not supported in PL/I program.

    If (2), try something like.
    Example 1:
    Code:
    SELECT (SELECT COUNT(DISTINCT OB)
             FROM  SESSION.BENST
             WHERE GROUP = 1 AND SPOL = '1') AS "1,1"
         , (SELECT COUNT(DISTINCT OB)
             FROM  SESSION.BENST
             WHERE GROUP = 1 AND SPOL = '2') AS "1,2"
         , (SELECT COUNT(DISTINCT OB)
             FROM  SESSION.BENST
             WHERE GROUP = 2 AND SPOL = '1') AS "2,1"
         , (SELECT COUNT(DISTINCT OB)
             FROM  SESSION.BENST
             WHERE GROUP = 2 AND SPOL = '2') AS "2,2"
         , (SELECT COUNT(DISTINCT OB)
             FROM  SESSION.BENST
             WHERE GROUP = 3 AND SPOL = '1') AS "3,1"
         , (SELECT COUNT(DISTINCT OB)
             FROM  SESSION.BENST
             WHERE GROUP = 3 AND SPOL = '2') AS "3,2"
         , (SELECT COUNT(DISTINCT OB)
             FROM  SESSION.BENST
             WHERE GROUP = 4 AND SPOL = '1') AS "4,1"
         , (SELECT COUNT(DISTINCT OB)
             FROM  SESSION.BENST
             WHERE GROUP = 4 AND SPOL = '2') AS "4,2"
     FROM  sysibm.sysdummy1
    ;
    Example 2:
    Note: WHERE clause might be unnecessary.
    Code:
    SELECT COUNT(DISTINCT
                 CASE
                 WHEN GROUP = 1 AND SPOL = '1' THEN OB
                 END ) AS "1,1"
         , COUNT(DISTINCT
                 CASE
                 WHEN GROUP = 1 AND SPOL = '2' THEN OB
                 END ) AS "1,2"
         , COUNT(DISTINCT
                 CASE
                 WHEN GROUP = 2 AND SPOL = '1' THEN OB
                 END ) AS "2,1"
    ...
    ...
    
         , COUNT(DISTINCT
                 CASE
                 WHEN GROUP = 4 AND SPOL = '2' THEN OB
                 END ) AS "4,2"
     FROM  SESSION.BENST
     WHERE GROUP IN ( 1 , 2 , 3 , 4 )
       AND SPOL  IN ( '1' , '2' )
    ;
    Last edited by tonkuma; 05-21-13 at 22:42. Reason: Add qualifier "SESSION." Replace GP in Example 1 with GROUP. Completely replace Example 2.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two questions.

    (1) What DB2 version/release are you using?

    (2) How did you included the query in the PL/I program?
    I think there are at least two ways.
    (2-1) DECLARE CURSOR -> OPEN -> FETCH -> CLOSE
    (2-2) SELECT INTO

  4. #4
    Join Date
    Feb 2012
    Posts
    5
    @tonkuma
    I'm sorry that I haven't replied yesterday. I was busy with testing it in SPUFI what wasn't easy because I didn't have proper table. After I tested in SPUFI I put it in the program and it works perfect. I decided to use second version even both version works. It's fantastic idea, it solved several problems I had.

    It's in my archive now. Thank you so, so much for your fast reply and help.

Posting Permissions

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