View previous topic :: View next topic
|
Author |
Message |
srajendran2
New User
Joined: 13 May 2008 Posts: 56 Location: Chennai
|
|
|
|
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 |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005 Posts: 306
|
|
|
|
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 |
|
|
srajendran2
New User
Joined: 13 May 2008 Posts: 56 Location: Chennai
|
|
|
|
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 |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005 Posts: 306
|
|
|
|
Whats the problem with that query. When I checked with some simple tables it was working |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
|