Regarding count and distinct in the generated SQL.

[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

[tupac_rd]

Can you create a seperate query for distinct values, and then use it as the source for the query you want to do the count.

[cognosbi]

Thanks for the response.

Tried what you have mentioned.

But the result was one more sub-select with the first select statement still having the count and distinct together.

i.e.
select count(distinct(PROD_CD)
from (
select (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

Need to get the distinct within the subselect and the count in the outer select...

like:

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

Hope this explains better.

Thanks,

[tupac_rd]

can you post the report xml

[cognosbi]

Thanks for your response again.

Providing report xml won't help much.
It just shows the structure of the single query there is.

And as far as the generated sql is concerned what I had mentioned, its the same.

Thanks in advance.

[Alp]

Hi Cognosbi,

I am not trying to challenge you so it is rather from curiosity ...

Do you think DISTINCT will add any value on top of GROUP BY in your sample? I am most definitely missing something, that why I am asking ...

- Alp

[jive]

HI CognosBi ,

If you used "group by col_a" and select distinct col_a. it's the same operation, plus you don't do any calculation just picking col_a by unique item. The solution is the group by. If this select is genenrated by cognos I think you have to check the determinant and if the group box is check.

Thanks

[cognosbi]

Sorry.My apologies.

There is no group by in the subselect.

It should be something similar to this:

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
   ) FETCH ONLY

[tupac_rd]

I am able to get the exact SQL what you are trying to do against our DB2 db. I am doing the same steps which I mentioned in my previous post.

I cannot post the report xml since it is not against sample database.

[cognosbi]

Appreciate if you can share the steps.

Have been trying all kinds of alternatives and options, but somehow I am not getting the required results.

Thanks in advance.

[tupac_rd]

Drag and drop a query (Query 1). Drag and drop Product Code. If you look at the query now it should say

select distinct "T1"."PROD_CD" "Product Code"
from "S1"."T1" "T1" FOR FETCH ONLY

Now drag and drop a second query (Query 2). Drag Query 1 next to Query 2 so that Query 1 is the source of Query 2.

Query 2 <-- Query1.
Double click on Query 2. From Insertable Objects, drag Product Code into Query 2. double click on Product Code and change Query1.Product Code to count(Query1.Product Code).

Now check the query. It should say

select count ("Query1"."Product_Code") "Product_Code"
from (
select distinct "T1"."PROD_CD" "Product Code"
from "S1"."T1" "T1") "Query1" FOR FETCH ONLY

Hope thats what you were looking for...

[cognosbi]

Oh ok...
I know what you are saying.
Yeah, have tried that. But this you can accomplish in RS.

What is required is to just pull the PROD_CD query item in RS and to do count in the aggregate property or use the aggregate in the expression. Using two querries in RS, although gives the correct result,that is not what is required.

Simply put, the distinct part of the sub-query should somehow should get handled in the FM.
And in the RS, drag drop that query item and do a count on it.
And when we look at the SQL generated by that Query in the RS, it should satisfy the sub select statement which I mentioned earlier...

Sorry, maybe I should have clarified this earlier as well.

Thanks.