issue with cognos SQL
when we are using the filter by creating a parameter in Report Studio
cognos is generating a cast function by default . any ideas ?
Thanks for the help
Can you provide a bit more detail. :)
sample SQL
select "DMND_FRCST"."DMND_CTGRY" "Demand_Category"
from "SPM_BI_SL"."DMND_FRCST_V" "DMND_FRCST"
where "DMND_FRCST"."DMND_CTGRY" = cast(? as char(20))
group by "DMND_FRCST"."DMND_CTGRY"
due to this performance is getting effected for the reports
The above SQL shows Cast in RS where as in Data Base the query runs with out Cast Function
cognos is generating a cast function by default
can you add a database function like to_char(?parameter?), just to rule out cognos doing it... and see if that helps.
Data Base : Tera Data
to_char doesn't support for Teradata
I suspect that the cast function is not the cause of your poor performance because it looks to me as though this is the type of SQL generated when using the 'Show MDX/SQL...' menu option. Cognos typically substitutes filter parameters within native SQL to 'cast(? char(20))...' when a parameter value has not been defined i.e. when the report has not been validated.
As a troubleshooting exercise I would suggest hard-coding the filters and then comparing performance. It's probably worth checking the native SQL after this as well.
Cheers
Thanks for your answers. I changed the options in Governor Settings and I don't see Cast function at report level.
Quote from: johny.cbi on 02 Sep 2010 03:49:32 PM
Thanks for your answers. I changed the options in Governor Settings and I don't see Cast function at report level.
What governor setting did you modify? As Coggod said above (I love the username, by the way! :) ), cast(? char(20)) is normally only shown for an unidentified parameter value, and would not be translated into the runtime query as the parameter would have been resolved at this point.
MF.