Intermediate Table Type

The Intermediate Table Type property determines the type of intermediate tables, also referred to as temporary tables, are used to execute the report. All reports can be executed using permanent and temporary tables. Reports can also be resolved by generating derived tables, common table expressions, and views. However, certain scenarios involving partitioning, outer joins, or analytical functions cannot use these alternatives. In this case, the Fallback Table Type property is used to execute the report.

Only UDB DB2 and Oracle 9i support common tables.

This setting can have a major impact on the performance of the report. Permanent tables are usually less optimal. Derived tables and common table expressions usually perform well, but they do not work in all cases and for all databases. True temporary tables also usually perform well, but not all databases support them. The default setting is permanent tables, because it works for all databases in all situations. However, based on your database type, this setting is automatically changed to what is generally the most optimal option for that platform, although other options could prove to be more optimal on a report-by-report basis. The database-specific settings are noted below.

Database	Default
DB2 UDB Ox/390	True Temporary Tables
DB2 UDB	Common Table Expression
Informix IDS and XPs	True Temporary Tables
SQL Server	True Temporary Tables
Oracle	True Temporary Tables
RedBrick	True Temporary Tables
Sybase ASE and IQ	True Temporary Tables
Teradata	Derived Table

The options for this property are:

• Permanent table
• Derived table
• Common table expression
• True temporary table
• Temporary view
• Use default inherited value

For examples of this property in use, see the System Administration Guide.