Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Count of the inner query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
srajendran2

New User


Joined: 13 May 2008
Posts: 43
Location: Chennai

PostPosted: Wed Aug 13, 2008 12:00 pm    Post subject: Count of the inner query
Reply with quote

I have a query as given below

Code:
SELECT                                     
FK_CHRG_TYP_CD,FK_CR_ACCT_CAT_CD           
FROM D627402P.CHRG_TYP_TRNSTN               
WHERE FK_TRAN_TYP_CD NOT IN ('008','009')   
AND FK_CR_ACCT_CAT_CD NOT IN ('0021','0022')
AND FK_CHRG_TYP_CD LIKE 'D%'               
OR FK_CHRG_TYP_CD LIKE 'S%'                 
GROUP BY FK_CHRG_TYP_CD,FK_CR_ACCT_CAT_CD   


The result of the query is

FK_CHRG_TYP_CD FK_CR_ACCT_CAT_CD
---------+---------+---------+---
DAA2 1848
DAA3 1849
DAA4 1847
DAA5 1858
DAA6 1856
DAA7 1846
DAA8 1864
DAA8 1863

Here if the FK_CHRG_TYP_CD has two values in FK_CR_ACCT_CAT_CD, i need the corresponding FK_CHRG_TYP_CD. I mean i need only DAA8 as it has two values in FK_CR_ACCT_CAT_CD. How can i achieve this in Db2 query?

[/code]
Back to top
View user's profile Send private message

Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Wed Aug 13, 2008 2:41 pm    Post subject: Reply to: Count of the inner query
Reply with quote

Assuming your requirement is to get FK_CHRG_TYP_CD value which has more than one occurance


Code:

SELECT                                     
FK_CHRG_TYP_CD,FK_CR_ACCT_CAT_CD,         
FROM D627402P.CHRG_TYP_TRNSTN               
WHERE FK_TRAN_TYP_CD NOT IN ('008','009')   
AND FK_CR_ACCT_CAT_CD NOT IN ('0021','0022')
AND FK_CHRG_TYP_CD LIKE 'D%'               
OR FK_CHRG_TYP_CD LIKE 'S%' 

and FK_CHRG_TYP_CD IN(SELECT FK_CHRG_TYP_CD FROM D627402P.CHRG_TYP_TRNSTN

GROUP BY FK_CHRG_TYP_CD 
HAVING COUNT(*) > 1
Back to top
View user's profile Send private message
srajendran2

New User


Joined: 13 May 2008
Posts: 43
Location: Chennai

PostPosted: Thu Aug 14, 2008 8:18 am    Post subject:
Reply with quote

Hi Prashanth

The query you gave did not work. Because for every value returned from the subquery, outer query is evaluated and i get "n" number of rows as a result. BTW i've found out the solution, it's something like this

Code:

SELECT                                     
DISTINCT FK_CHRG_TYP_CD,FK_CR_ACCT_CAT_CD, 
COUNT (DISTINCT FK_CHRG_TYP_CD)             
FROM D627402P.CHRG_TYP_TRNSTN               
WHERE FK_TRAN_TYP_CD NOT IN ('008','009')   
AND FK_CR_ACCT_CAT_CD NOT IN ('0021','0022')
AND FK_CHRG_TYP_CD LIKE 'D%'               
OR FK_CHRG_TYP_CD LIKE 'S%'                 
GROUP BY FK_CHRG_TYP_CD,FK_CR_ACCT_CAT_CD   
HAVING COUNT(DISTINCT FK_CHRG_TYP_CD) > 1   
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Thu Aug 14, 2008 10:55 am    Post subject: Reply to: Count of the inner query
Reply with quote

Whats the problem with that query. When I checked with some simple tables it was working icon_question.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Aug 14, 2008 8:17 pm    Post subject:
Reply with quote

Hello,

Quote:
The query you gave did not work.
If you believe this, you need to show the data used, the result you expected, and the result you actually received.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us