Solved

DB2 SQL question

Posted on 2013-01-09
9
744 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
9 Comments
 
LVL 45

Accepted Solution

by:
Kdo 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

930 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now