Supported SQL types

IBM® Cognos® Analytics supports three types of SQL: Cognos SQL, native SQL, and pass-through SQL.

Cognos SQL

Cognos SQL is an implementation of the standard SQL. It works with all relational and tabular data sources. This is the optimal type of SQL for use with Cognos Analytics applications. Using Cognos SQL is preferable because you can have fewer database restrictions. Cognos SQL improves table performance by, for example, removing unused elements at query time.

Cognos SQL does not support non-standard SQL.

Native SQL

Native SQL uses vendor-specific SQL constructs. Use native SQL to pass the SQL statement that you enter to the database. Cognos Analytics might add statements to what you enter. A native SQL statement can reference only one data source.

This type of SQL must be completely self-contained. It can't reference anything outside that SQL, such as database prompts, variables, or native formatting that would normally be supplied by the calling application.

If you are comfortable working with a native SQL version, you can use keywords that are not available in Cognos SQL, and copy and paste SQL statements from other applications to Cognos Analytics.

This type of SQL might not work with a different data source.

Pass-Through SQL

Use pass-through SQL when the SQL statement that you enter is not valid inside a derived table, such as in a With or OrderBy clause. Generally, you should use pass-through SQL only if you must create a table that contains source-specific constructs.

Pass-through SQL lets you use native SQL without any of the restrictions that the data source imposes on subqueries (pass-through SQL tables are not processed as subqueries). Instead, the SQL for each table is sent directly to the data source where the query results are generated.

Performance is slower because each table is sent to the source as a separate statement rather than being optimized by Cognos Analytics. Therefore, when choosing between native SQL and pass-through SQL, you must decide which is more important: performance or using SQL that is not permitted in a subquery.

Pass-through SQL must be completely self-contained. It must not reference anything outside of that SQL, such as database prompts, variables, or native formatting that would normally be supplied by the calling application.

A pass-through SQL statement might not work with a different data source.