You can add structure to your domain using SQL queries (as derived tables) and simple calculations (as calculated fields). Once created, derived tables and calculated fields can be used like any other Domain components, for example, to create joins, define additional calculated fields, or create pre-filters. They can also be referenced in the Domain's security file and locale bundles.
Derived Tables
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. |
Calculated Fields
You create a calculated field for a Domain in two ways:
| • | By writing an expression that computes a value based on the data in one or more columns in a single table or join tree. All columns in the expression for a calculated field must be from the same join tree. |
| • | By creating a constant expression. For example, you might create an integer field named Count that has the value 1 and later has a default summary function to count all occurrences. |
| Calculated field expressions use the Domain Expression Language, fully described in The DomEL Syntax. |
Calculated fields appear in the Data Structure panel on the Joins, Pre-filters, and Data Presentation tabs. They are identified by a distinctive icon , and the field name is shown in bold. In addition, if a calculated field is used by another calculated field, its name is also in italics. For example, supposed you have defined calcField1 and then you create calcField2, using calcField1 in its expression. calcField1 will be shown in bold and italics in the Data Structure panel.
Calculated fields from a table or join tree appear under their parent in the Data Structure panel. Global constant fields appear above the data source.
| Calculated fields may be used to compute other calculated fields. |
To create a calculated field for a table or join tree:
| 1. | Right-click on the table or join tree on the Joins tab and select Create Calculated Field. The New Calculated Field dialog appears. |
New Calculated Field |
|
| 2. | In Field Name, enter the name you want to use for the calculated field. This name becomes the field's ID in the Domain. |
| After it has been created, you can give the calculated field a more meaningful label and full description on the Data Presentation tab. |
| 3. | In Data Type, select a datatype for the calculated field. The expression you write must return a value of this type. |
| Generally, this datatype matches the datatype of the columns in the expression. Therefore, you need to be familiar with the datatypes of columns in the data source. |
| 4. | Enter an expression for the calculated field in the Formula text box: |
| • | To insert a reference to the value of another column, find it in the Available Fields list and double-click the column name. The column name appears in the expression at the cursor, qualified by its table name. You can also type a column name directly in the Formula box, in the format tablename.fieldname. |
| • | To add a supported operator, click the operator icons below the Formula box, or type the operator directly. |
| • | 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. |
| Calculated field expressions use the Domain Expression Language, fully described in The DomEL Syntax. |
| 5. | Click Validate to verify the calculated field is valid. You must fix any errors before you can save; the error message can help you with this. |
| 6. | Click Create Calculated Field to save the new calculated field. |
If the calculated field is valid, it appears in the Data Selection panel under the table or join tree you chose. A distinctive icon identifies it as a calculated field.
To create a constant field:
An expression that uses no columns has a constant value. For example, you might create an integer field named Count that has the value 1 and later has a default summary function to count all occurrences. Constant fields are independent of join trees and appear at the same level as join trees in the Data Structure panel.
| 1. | Select Create Constant Field… from the |
Creating a Constant Field |
|
| 2. | In Field Name, enter the name you want to use for the calculated field. |
| 3. | In Data Type, select a datatype for the calculated field. |
| 4. | Enter a constant expression for the calculated field in the Formula text box: |
| • | To add a supported operator, click the operator icons below the Formula box, or type the operator directly. |
| • | 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. |
| 5. | Click Validate to verify the calculated field is valid. You must fix any errors before you can save; the error message can help you with this. |
| 6. | Click Create Calculated Field to save the new calculated field. |
If the constant field is valid, it appears in the Data Selection panel under the Constant Fields node . A distinctive icon identifies it as a calculated field.
To edit a calculated field:
| 1. | Right-click the calculated field in the Data Structure panel on the Joins tab and select Edit Calculated Field. The Edit Calculated Field dialog appears. |
| 2. | Modify its name, its type, or its expression. |
| 3. | Click Update Field to save your changes. |
To delete a calculated field:
| 1. | Right-click the calculated field in the Data Structure panel on the Joins tab and select Remove Calculated Field. |
Recommended Comments
There are no comments to display.