[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DB2 SQL question

Posted on 2013-01-09
9
Medium Priority
?
752 Views
Last Modified: 2013-08-02
I need different counts in a horizontal instead of vertical. I have written a sql which is working

SELECT COUNT( CASE CO_REFERRAL
WHEN 'INIT' THEN 0            
 END) AS INITIAL_REF          
 ,COUNT( CASE CO_REFERRAL_TYPE
  WHEN 'HWYP' THEN 0          
  END) AS HIGHPATROL_REF      
 ,COUNT( CASE CO_REFERRAL_TYPE
    WHEN  'POSITIVE' THEN 0      
  END)        


However i need multiple counts from different tables. The above is from one table.
If i need multiple counts from different tables horizontally how can i do it.

1 count can have a join with other table.  another count could be from a totally new table, but relevant to all info i am showing
0
Comment
Question by:kanneswara
  • 4
  • 3
7 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 38759034
Hi  kanneswara ,

Just write them as separate inline queries.

SELECT 
  (SELECT count (*) from table1),
  (SELECT count (*) from table2),
  (SELECT count (*) from table3)
FROM sysibm.sysdummy1;

Open in new window



Good Luck,
Kent
0
 

Author Comment

by:kanneswara
ID: 38759060
What is sysibm.sysdummy1. do we need it

Regards
0
 

Author Comment

by:kanneswara
ID: 38759080
Hello Kent [Guruji],


It is working. Can you be kind eneough to share the funda on whic this works.

Regards
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38759086
That's a dummy name.  

DB2 SQL requires a table name.  (MicroSoft SQL does not.)  And IBM provides that name as a dummy.  Oracle uses DUAL for its dummy table name.  (IBM also uses DUAL in some of its newer releases.)

Those kinds of queries can also be written without using the dummy table.  But I think that the reference to the dummy table makes the SQL neater.

SELECT count (*),
  (SELECT count (*) from table2),
  (SELECT count (*) from table3)
FROM table1;

Open in new window



Kent
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38759100
All modern SQL supports queries of single tables, multiple tables, sub-selects, etc.

The first example uses three sub-selects, one to acquire each count.  If you generate an explain plan you'll see that DB2 executes each sub-select to get each of the counts, then puts the results together for the final (outer) query.


Kent
0
 

Author Comment

by:kanneswara
ID: 38759117
Hi

really sorry, but this is what i wrote

SELECT                                                                          
 (SELECT COUNT(*)                                                              
         FROM SFAMD500.SFAMT677_DRG_T_REF                                      
        WHERE CO_REFERRAL_TYPE = 'INIT'                                        
       AND DA_REFERRED >= '2010-12-15'                                          
       AND   DA_REFERRED <= '2020-12-15')                                      
 (SELECT COUNT(*)                                                              
         FROM SFAMD500.SFAMT677_DRG_T_REF                                      
        WHERE CO_REFERRAL_TYPE = 'HWYP')                                        
  FROM SYSIBM.SYSDUMMY1;                                                        
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE
         LEGAL ARE: MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE      
         HOURS                

i am getting the above error
0
 

Author Comment

by:kanneswara
ID: 38759127
Please ignore the above question. I got excited and did a mistake
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month20 days, 6 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question