Count & distinct in sub select in the generated query SQL

[tektipscognos]

Here is the situation.

Need to get a sub-select statement generated in the generated sql within Report Studio, when a single query item (PROD_CD) or a set of query items is pulled in the report.

Something similar to

select count(PROD_CD)
from
(select distinct(PROD_CD) from T1 T1 join T2 T2
on T1.xyz = T2.xyz
where filter1 and filter 2
group by T1.PROD_CD) FETCH ONLY

The problem is, the count and the distinct are showing up together in the SQL like: select count(distinct(PROD_CD) ......

I need to have the distinct inside the subselect and the count in the select portion of the query.

Tried using 'group by' in the determinants on PROD_CD, but still no much luck.

At report level only the count aggregation should happen....nothing else, the SQL generated from the query used in the report should be something as mentioned above...

Appreciate if you can share some ideas...

Thanks

 

[blom0344]

select count(distinct PROD_CD) from T1 join T2   
on T1.xyz = T2.xyz   
where filter1 and filter 2

should suffice. The 'group by' is redundant in any case..

Ties Blom