If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Count & Distinct in sub select in the query SQL

Started by cognosbi, 02 Aug 2010 10:36:37 AM

Previous topic - Next topic

cognosbi


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