Solved

DB2 SQL question

Posted on 2013-01-09
9
749 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 45

Accepted Solution

by:
Kent Olsen earned 500 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
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 
LVL 45

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 45

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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 (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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

687 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