Solved

DB2 SQL question

Posted on 2013-01-09
9
743 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

8 Experts available now in Live!

Get 1:1 Help Now