Because each query subject is sent to the data source as a separate statement rather than being optimized by IBM® Cognos® Framework Manager, performance is slower. Therefore, in 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.
Generally, you should use pass-through SQL only if you must create a query subject that contains
constructs that are specific to a data source and that cannot be used inside a derived table, such
as in a
SQL specified in Framework Manager and processed by the database, whether native or pass-through, 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.
For example, here is a systems-oriented report that contains the system date:
SELECT TO_CHAR(SYSDATE, 'DAY, DDTH MONTH YYYY') FROM SYS.DUAL
Note that the number sign (#) is reserved for macros and that column names must be unique. Framework Manager removes anything that is outside the number signs when running the macro.