Constructs of the SQL standard

If the data source supports it, you can use the With clause with Cognos® SQL. The With clause is used to generate more readable SQL and to let the data source generate a more optimal plan for data retrieval. The data source can more easily detect the cases where the same tables must be scanned and can then resolve these as an inline view or temporary table.

By default, IBM® Cognos Framework Manager uses the common table constructor from the SQL standard when the Use With clause when generating SQL governor is set.

Use the With clause for better query performance if the request is restricted to functionality supported by the underlying data source software. When a request uses functionality that is not supported by the data source, using the With clause may cause additional decomposition of the query, which can lead to degraded performance. In this case, not using the With clause may generate a better set of queries to the underlying data source.

Here is an example of Cognos SQL using derived tables:

SELECT * 
  FROM 
       (SELECT SNO C1, AVG(QTY) C2, COUNT(*) C3 FROM SUPPLY GROUP BY SNO) T1,
       (SELECT MAX(QTY) C1 FROM SUPPLY) T2

The following shows how Cognos SQL turns the above example into a With clause:

WITH T1 AS 
        (SELECT SNO C1, AVG(QTY) C2, COUNT(*)C3 FROM SUPPLY GROUP BY SNO), T2 AS 
        (SELECT MAX(QTY) C1 FROM SUPPLY) SELECT *FROM T1, T2

Do not use the With clause for recursive processing.

For more information about the With clause, see Governors.

Data type checking and SQL validation are continually being improved. Because of this and because not all vendors are completely compliant with the SQL standard, invalid or ambiguous SQL expressions that previously were passed to the data source will no longer be passed down. If you have an expression that returns a data type not specified by the SQL standard, pass the expression to the data source by using the syntax {expr}. Your users should use the same technique.