Count query

[rk68]

I am using the following query to get members (DMA_CODE) using certain conditions i.e. where clause
===QUERY 1 ========
Select * from DB2ADMIN.T_ANL_AMT a, DB2ADMIN.T_TIRE_MST b
where anl_prg_id='HR' and length(a.anl_dma_code) > 8 and
a.ANL_TRN_TYPE = 'D' AND a.ANL_YEAR = 2004 AND
a.ANL_MONTH = 10 AND a.ANL_DMA_CODE not in
( select CUS_DMA_CODE from t_cus_nap) AND
a.ANL_AMT between b.TIR_YR0_LO_AMT AND TIR_YR0_HI_AMT AND
b.TIR_YR0_ACTV_FL = 'Y' AND b.TIR_TIRE_ID != 'NO TIER' AND b.tir_prg_id ='HR'
=============

Now I also need to see the members (DMA_CODE) which I get by
executing the above query also has transactions for more than 5 months. There is a table which has all transaction of DMA_CODE, wherein I have to get the distinct count of dma_code which I get by executing the above dma_code.
====QUERY 2 ===
Select ctr_dma_code,count(distinct(ctr_adj_mm))
from DB2ADMIN.T_CLB_TRN
=========
1) is it possible to combine query 2 to query 1 to check the resultant member also exist in transaction table for more than 5 months. If yes, how can I write the query.
This is being done in a program (using Java).

Thnks
RAJ

 

[lespaul]

Yes, you can do this, but you can't use the join technique you've used.

SELECT A.FIELD1, A.FIELD2, B.FIELD1, B.FIELD2, COUNT(distinct(ctr_adj_mm))
FROM DB2ADMIN.T_ANL_AMT a
JOIN DB2ADMIN.T_TIRE_MST b ON a.SOMEFIELD = b.SOMEFIELD
JOIN DB2ADMIN.T_CLB_TRN c ON a.SOMEOTHERFIELD = c.SOMEOTHERFIELD
WHERE anl_prg_id='HR' and length(a.anl_dma_code) > 8 and 
a.ANL_TRN_TYPE = 'D' AND a.ANL_YEAR = 2004 AND
a.ANL_MONTH = 10 AND a.ANL_DMA_CODE not in 
( select CUS_DMA_CODE from t_cus_nap) AND        
a.ANL_AMT between b.TIR_YR0_LO_AMT AND TIR_YR0_HI_AMT  AND
b.TIR_YR0_ACTV_FL = 'Y' AND b.TIR_TIRE_ID != 'NO TIER' AND b.tir_prg_id ='HR'
GROUP BY A.FIELD1, A.FIELD2, B.FIELD1, B.FIELD2
HAVING COUNT(distinct(ctr_adj_mm)) > 5

since you have to list each field in the group by when you use an aggregate function, you can't use the * to get all the fields. You will need to list each field and you will need to manually join the tables on a specific field or fields. So, I added the GROUP BY clause for the aggregate and then you use the HAVING clause to eliminate those that don't have more than 5 counts.

Without a little more information about your field names and how they relate together, I can't help any more.
HTH

Leslie