IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Count of the inner query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
srajendran2

New User


Joined: 13 May 2008
Posts: 56
Location: Chennai

PostPosted: Wed Aug 13, 2008 12:00 pm
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
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: 56
Location: Chennai

PostPosted: Thu Aug 14, 2008 8:18 am
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
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

Moderator Emeritus


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

PostPosted: Thu Aug 14, 2008 8:17 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts RC query -Time column CA Products 3
No new posts Validating record count of a file is ... DFSORT/ICETOOL 13
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top