Select vs. Select Distinct with Calc

[stewm]

Hello,

I recently created a report and when I looked at the SQL it produced it exactly what I wanted...

SELECT DISTINCT..FROM...WHERE...ORDER BY.

At the end I wanted to display the data prompt values formatted to Monthname, Day, Year to Monthname, Day, Year. I created a calculation for this. Why when you do this...does your SQL statement no longer show DISTINCT? I tried to group, associate, ungroup, sort, unsort, everthing with the calculation but the SQL remained just a SELECT. When I delete the calculation from the report the SELECT DISTINCT comes right back. Can anyone explain this functionality of Impromptu?

Thanks,

Mark

 

[hindsjl]

Mark -

I haven't hit exactly this problem before, but here are some possibilities:

1. Check whether your calculations are based _directly_ on the prompts, or are based only indirectly on them (i.e., based on objects from the database which the Filter says ought to match the prompts). Usually better to base directly on the prompts, which are passed as constants in the SQL statement.

2. Check how your prompts are used elsewhere in the report, especially in the Filter. If they are used in IN or BETWEEN expressions, rather than just EQUALS expressions, Imp may be assuming that multiple values are possible, and confuses itself somehow over the Distinct.

3. Check your Report Frame's "Scope of Data". It may help to make sure that each database object "joined" to your prompts can have only one value at that level of aggregation.

Hope this helps,
John

 

[griffindm]

mark,

Make sure your calculations do not have any local-only functions in them. If so, you will lose the "Distinct" clause in the SQL. Try to find a database function that will do the same thing.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com