IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Selecting multiple constant records


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
mbattu

New User


Joined: 19 Jan 2011
Posts: 13
Location: Toronto

PostPosted: Tue Feb 02, 2021 1:18 am
Reply with quote

Hi,

I have a set of companies that have to be fetched from a Table. This table can have multiple rows, single row or no row for a given Company. Therefore requirement is that if data is present in the Table, display Company & Count(rows). If not still display the Company with count 0.


Example:-
Input given

AAA1
BBB2
CCC3
DDD4


Example Data in Table say Company_TBL
-------------------------------
Col1 Col2 Col3
AAA1 10 20210101
AAA2 20 20210115
CCC3 11 20210201
DDD4 05 20210201


Desired Output:-
-----------------------
AAA1 2
BBB2 0
CCC3 1
DDD4 1


To accomplish this my approach was to write two sub-queries, first one to fetch the details from COMPANY_TBL and other is to declares all the companies as constants with value zero. Perform UNION of both.

Problem with this solution is I am not sure of DB2 equivalent of below PostgreSQL. I can write single row constants for each Company records using SYSIBM.SYSDUMMY1 but I want to know if there is any better way to this.

Code:
SELECT   *
   FROM   ( VALUES
                             ('AAA1', 0),
                             ('BBB2', 0),
                             ('CCC3', 0),
                             ('DDD4', 0)
               ) AS TEMP_TBL (COL1, COL2)
;



Below is the complete query I am thinking of

Code:
SELECT   COL1
             ,SUM(COL2)
   FROM  (
                SELECT  COL1
                            ,COUNT(COL1)
                   FROM  COMPANY_TBL
                  WHERE  COL1   IN  ('AAA1', 'BBB2', 'CCC3', 'DDD4')
                  GROUP BY  COL1

                 UNION
 
                 SELECT   *
                   FROM   ( VALUES
                                            ('AAA1', 0),
                                            ('BBB2', 0),
                                            ('CCC3', 0),
                                            ('DDD4', 0)
                                ) AS TEMP_TBL (COL1, COL2               
               )
GROUP BY   COL1

;
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2790
Location: NYC,USA

PostPosted: Tue Feb 02, 2021 4:07 am
Reply with quote

How did you get AAA1 2? Second ,what is the input , is it always constant ?

You can do a left join on the Company_TBL , ( by adding those hardcoded values in the where) and then do a Group By and Count (*) on main SELECT, make sure to handle NULL to 0.
Back to top
View user's profile Send private message
mbattu

New User


Joined: 19 Jan 2011
Posts: 13
Location: Toronto

PostPosted: Tue Feb 02, 2021 4:40 am
Reply with quote

Rohit Umarjikar wrote:
How did you get AAA1 2? Second ,what is the input , is it always constant ?

Thank you for the reply.

Sorry, I gave wrong COMPANY_TBL data, corrected it now. Yes, input COMPANY list is constant.

Code:
Col1   Col2   Col3
AAA1   10     20210101
AAA1   20     20210115
CCC3   11     20210201
DDD4   05     20210201



Rohit Umarjikar wrote:
You can do a left join on the Company_TBL , ( by adding those hardcoded values in the where) and then do a Group By and Count (*) on main SELECT, make sure to handle NULL to 0.

I didn't get this part. How do I bring those hardcoded value to resource section by adding them in condition section (WHERE).

For now I am doing this way which I feel is not good

Code:
SELECT   COL1
        ,SUM(COL2)
   FROM  (
                SELECT  COL1
                       ,COUNT(COL1)
                   FROM  COMPANY_TBL
                  WHERE  COL1   IN  ('AAA1', 'BBB2', 'CCC3', 'DDD4')
                  GROUP BY  COL1

                 UNION
 
                 SELECT   'AAA1', 0   FROM SYSIBM.SYSDUMMY1

                 UNION

                 SELECT   'BBB2', 0   FROM SYSIBM.SYSDUMMY1   

                 UNION

                 SELECT   'CCC3', 0   FROM SYSIBM.SYSDUMMY1   

                 UNION

                 SELECT   'DDD4', 0   FROM SYSIBM.SYSDUMMY1   
                   
               )
GROUP BY   COL1

;
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2790
Location: NYC,USA

PostPosted: Tue Feb 02, 2021 11:15 pm
Reply with quote

Code:
 SELECT  COMPANY_TBL.col1, count(*) FROM                 
(SELECT 'AAA1' as col1,10,'20210101' FROM SYSIBM.SYSDUMMY1
 UNION
 SELECT 'AAA1' as col1,20,'20210115' FROM SYSIBM.SYSDUMMY1
 UNION
 SELECT 'CCC3' as col1,11,'20210201' FROM SYSIBM.SYSDUMMY1
 UNION
 SELECT 'DDD4' as col1,05,'20210201' FROM SYSIBM.SYSDUMMY1) COMPANY_TBL
 GROUP BY COMPANY_TBL.col1 
 UNION
 SELECT  static_tbl.col1,0 FROM
                   (SELECT   'AAA1' as col1  FROM SYSIBM.SYSDUMMY1
                    UNION
                    SELECT   'BBB2'as col1  FROM SYSIBM.SYSDUMMY1   
                    UNION
                    SELECT   'CCC3'as col1  FROM SYSIBM.SYSDUMMY1   
                    UNION
                    SELECT   'DDD4'as col1  FROM SYSIBM.SYSDUMMY1) static_tbl 
  WHERE NOT EXISTS (
  ( SELECT 1 FROM                             
     (SELECT 'AAA1' as col1,10,'20210101' FROM SYSIBM.SYSDUMMY1
      UNION
      SELECT 'AAA1' as col1,20,'20210115' FROM SYSIBM.SYSDUMMY1
      UNION
      SELECT 'CCC3' as col1,11,'20210201' FROM SYSIBM.SYSDUMMY1
      UNION
      SELECT 'DDD4' as col1,05,'20210201' FROM SYSIBM.SYSDUMMY1) COMPANY_TBL1
      where  static_tbl.col1 = COMPANY_TBL1.col1
  ) 
                   ) 
  ORDER BY 1,2

Output:
Code:
 COL1 2
 ---- -
 AAA1 2
 BBB2 0
 CCC3 1
 DDD4 1
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
This topic is locked: you cannot edit posts or make replies. Sort to include records of file 2 int... Java & MQSeries 1
No new posts define 1 DCB parms for multiple outpu... JCL & VSAM 9
No new posts Multiple find/replace on the same dat... DFSORT/ICETOOL 4
No new posts Copy a DS Member to multiple users at... TSO/ISPF 8
No new posts Cobol program to determine the sequen... COBOL Programming 2
Search our Forums:

Back to Top