difference in FM SQL

[JPCogAdmin]

What are the major differences between these 3 types of SQL in Framework Manager. I'm sure they put them in because they serve a different purpose?

Cognos SQL.
Native SQL
Passthrough SQL

Also, can any of these accept a prompt parameter to be passed in?

Thanks

-Jim

 

[mfgf]

Hi,

Cognos SQL is an ansi-standard version of the language that is not specific to any database. If you code a query in Cognos SQL, it gets translated into the native SQL of your database, optimised, validated, then passed off to the database to be executed.

Native SQL is a dialect specific to the particular database you are using. So for example, if your data is in ORACLE, you can code a query in ORACLE SQL. This then gets optimised and validated at runtime then passed to the database to be executed.

Passthru SQL is again a dialect specific to the particular database you are using. However, this time the query does not get validated or optimised by the Cognos software before it gets passed to the database, so what you code is exactly what gets sent - no more, no less. If, for example, your query does not have a stable projection list, this would be added locally by the Cognos server once the result set has been returned, whereas with a Native query the Cognos server would include your query as a derived table (inline view) within an outer query generating the projection list, then pass this off to the database to be processed.

Best regards,

MF.

 

[JPCogAdmin]

Thank you MF.
That's really good. Thank you once again.

-Jim