issue with cognos SQL

[johny.cbi]

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

[technomorph]

Can you provide a bit more detail. :)

[johny.cbi]

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

[tupac_rd]

can you add a database function like to_char(?parameter?), just to rule out cognos doing it... and see if that helps.

[johny.cbi]

Data Base : Tera Data

to_char doesn't support for Teradata

[technomorph]

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

[johny.cbi]

Thanks for your answers. I changed the options in Governor Settings and I don't see Cast function at report level.

[MFGF]

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.