why generated sql is different

[tmrkill]

In report studio, when creating a query of a direct SQL, and typed "select A from T1" in the SQL box, the generated sql becomes "with SQL1 (select A from T1) select A from SQL1 for fetch only" (something like that).
Is there any configuration that it would make the generated sql exactly the same as what is typed in the SQL box? Thanks.

We are upgrading our Cognos server to 10.2 and we found some existing reports have SQL in this form:
with xxx as (...), yyy as(...) select * from ...

However, in 10.2.2, the generated sql wrapped another 'WITH', then nesting WITH statements caused the error.

[saumil287]

Hi,

Cognos generates 2 sql types

cognos sql and native sql.

Cognos first uses its own sql and then internally converts the sql and pass to database.

The cognos sql is having a with clause by default, There is also an option whether you want to use With clause or not from FM.
In FM, go to settings(I don't exactly remember) . Project settings
there will be a property Use With clause, Uncheck it.

Regards
saumil

[AnalyticsWithJay]

Is there any configuration that it would make the generated sql exactly the same as what is typed in the SQL box? Thanks.

The default behavior is to apply a WITH clause when it goes through the optimizer. You can turn this setting off at model level or at report level.

- In FM, go to Project -> Edit Governors -> "Use WITH clause when generating SQL"
- In Report Studio, select the query and you will see a Use With Clause property in the properties pane.

In most reporting environments you'll see performance improvements while using a WITH clause, as the DB can re-use a sub-query instead of re-computing it. There are instances where the WITH clause can cause performance degradation, and disabling it in those individual cases, as opposed to disabling for the package, is my recommendation.