You can add tables to your Domain based on SQL queries of the chosen data source. These are called derived tables, and once created, they can be used like other tables to create joins and then displayed as sets and items. They can also be referenced in the Domain's security file and locale bundles.
You create a derived table in a Domain using a custom query. Because Domains are based on JDBC and JNDI data sources, you write the query in SQL. You run the query, and then select columns from the query result for use in the Domain design.
For example, with a JDBC data source, you can write an SQL query that includes complex functions for selecting data. You can use the items in a derived table for other operations on the Domain, such as joining tables, defining a calculated field, or filtering. The items in a derived table can also be referenced in the Domain's security file and locale bundles.
| | The clauses in a query determine the structure and content of the table returned by the query. For example, the WHERE clause may contain conditions that determine the rows of the derived table. |
To define a derived table:
| 1. | Right-click the data source node on the Data Management tab and select Create Derived Table… OR click  |
The New Derived Table dialog appears.
| New Derived Table dialog |
| |
| 2. | Type a name for the table in the Derived Table Name field. |
| 3. | Enter a valid SQL query in Query. Only queries that begin with the SELECT statement are allowed. Stored procedures and functions are supported. Do not include a closing semi-colon (;). |
To use an attribute, enter {attribute('AttributeName')} or {attribute('AttributeName', 'Level')}. This must be a single-valued attribute; collections can't be used. See Using Attributes in the Domain Designer for more information.
| 4. | When the query is complete, click Run Query to test it. If the query is successful, the resulting fields are displayed in the Query Result list. By default, all columns in the result are selected. |
| 5. | Ctrl-click fields in the Query Result list to change the selection. If you want only a few columns out of many, it may be easier to specify the column names in the SELECT clause of the query. |
| 6. | Click Create Derived Table. |
The derived tabled is added under the Derived Tables node in the Data Structure panel. A distinctive icon 
identifies it as a derived table.
To copy a derived table:
| 1. | Right-click the derived table in the Data Structure panel, and select Copy Table. |
To edit a derived table:
| 1. | Right-click the derived table in the Data Structure panel, and select Edit Derived Table…. The Edit Derived Table dialog appears. |
| 2. | Edit the Derived Table Name, Query, and selected tables in the Query Result list. |
| 3. | Click Update Derived Table to save changes. |
To remove a derived table:
| 1. | Right-click the derived table in the Data Structure panel, and select Remove Derived Table…. |
| | You can't copy, edit, or remove derived tables on the Data Presentation tab. |
Recommended Comments
There are no comments to display.