The rules and behavior of a derived table include:
A Derived Table only has two speed menu options: “Add Selected Items” and “Remove”.
When you modify a Query section that is used as a Derived Table:
Derived tables are available for connections to Teradata, DB2, Oracle, and Microsoft SQL Server. If you use the feature when you build a query, an active connection is required if the server code is ODBC or OLEDB so that the “smart ODBC” can determine the database type.
All client versions of Interactive Reporting use derived tables. Scripts in Interactive Reporting document file opened with EPM Workspace can also use this feature.
If you remove all items from the Request line, the topic are displayed empty (there are no columns) in the section(s) where they are used as a derived query.
If the columns referenced by the “deriving” Query section are removed from the Request line, the effect is the same as if a “sync with database” had found the column removed.
If you rename items in a Query section used as a derived table, you must ensure that the new name is a valid name for the database in use. These names are used during the SQL generation, and are quoted if “quoted identifiers” are selected in the database connection.
Items from “Local Results” collection cannot be added to the Query.
The icon used for a derived table in the Table Catalog tree is shown on the left.
Both the referencing query and the derived table query must use the same data source even if one or the other is not connected. For example, assume Query1 is built from DataModel1, and Query2 is a query section not derived from a DataModel section. That is, you must be sure that both Query1 and Query2 can connect to the same data source name (such as an ODBC data source name or an Oracle TNS name), and they share the same API and server codes. The data source name alone may not be sufficient since two names can be identical over two different APIs. However, it is not required that the connections use the same database credentials. You do have to ensure that the referenced tables are accessible from whatever logon is used in all selected Query sections.
Interactive Reporting Web Client users need at least a Query role to use derived tables in a data model, or data model role to add or remove them from the data model.
To build a query that uses a derived table:
Build the query which will use the “derived table” and process the query.
Insert a new query by choosing New Query on the Insert menu.
Build the “Derived Table” query by clicking anywhere in the Catalog pane and selecting Derivable Queries on the shortcut menu.
The “Derivable Queries” option is not displayed if no query sections can be used in the current query.
(The Table Catalog below shows the Derivable Queries tree expanded and the Tables tree contracted. Local Results only are displayed when a user has requested them from the speed menu.)
The only queries that are displayed in this list are those that:
The topic name, when a derived table is added to the work area, is the same as the Query section name, and the column names are the same as the names of the items on the Request line in the Query section being added with the exception of any (data) function component. In cases where the function is displayed on the Request line, the resulting topic item name would be the same as the name that is displayed in Results after processing the query. The following diagram illustrates how the Request line for Query2 are displayed as a topic in another Query section.
Create a manual join by dragging an item from at least one topic to another, including to/from the added derivable query sections.
Once the derived table becomes a topic, items from it can be added to the Request, Filter, or Sort lines of the containing query. It can be referenced in computed item dialogs, and can be used in custom GROUP BY logic.
If you use the Show Values feature when setting a filter on an item in the “derived table”, the SQL that would be used is the same as if you set a filter on the same column in the source Query section.
Process the query using the derived table.
You can process the query by way of “Process” command when viewing the query section (or one of its dependent sections) or by checking it in the Process Custom dialog.
The “Process All” command processes the query containing the derived table. It also processes the query section from which the derived table is derived, unless it explicitly removed using the Query Processing Order dialog (which is recommended to avoid duplication of processing at the database.)
Additions to the Request line of the source Query are reflected in the topic item list of the referencing Query section the next time it is displayed.
Once a Query section has been added to another Query or Data Model works area, changes to the source query section will check for dependencies and warn you of any discrepancies that might occur. For example, you might receive a warning if an item was removed from the source query’s Request line which is used someplace else (Request, Filter, Sort, etc.) in a Query that is deriving a table from the source query.