Generated Native SQL vs Tools Native SQL

Cognos doesnt always run the Native SQL that you see from the Tools / Generated SQL/MDX Option. There are scenarios wherein it runs the Native SQL that you see from the Query Level Generated SQL options.

I have a report with some columns and count(distincts). The report also includes a footer that should display count(distincts ) at the footer level rather than the sum.

The Native SQL from Tools/Generated SQL/MDX generates a SQL:

Sel T1.C1 attribute_1, T1.C2 attribute_2,
T1.C3 Qty_A, T0.C3 Qty_A_Footer
from ()T0, ()T1
where....

and the Native SQL from the Query Level Generated SQL options does not include the footer level calculation. Hence the SQL would look like:

Sel attrib_1, attrib_2, count(distinct fact_1) from....

Now I would expect Cognos to fire the Native SQL from the Tools/Generated SQL/MDX options but that is not the case.

Cognos fires the Native SQL from the Query Level Generated SQL. The reason in this case was due to some fields that required Cognos functions. Once I changed the fields to do a DB only processing and included DB only functions Cognos started firing the Native SQL from the Tools option. Not sure if this is a scenario with Teradata only or if this is how Cognos is intended to behave.

Planning on doing some more research on this. Will update when I do so.

Tip: To be on a safer side always verify both the Native SQLs to ensure that both the SQLs are what you would expect to be fired against the DB.