[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

DB2 SQL question

Posted on 2013-01-09
9
Medium Priority
?
751 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 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