Derived Tables Tab

You create a derived table in a Domain by first building a custom query using the objects you selected on the Tables tab. 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.

The clauses in a query determine the structure and contents 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. Type a name for the table in the Query ID field.
2. Enter a valid SQL query in Query. The query may refer to any table or column in Available objects. Only queries that begin with the select statement are allowed. Do not include a closing semi-colon (;).

Expand the tables in Available objects. Double-click column names to add them to the query.

3. When the query is complete, click Run Query to test it and choose the list of columns in the result.
4. By default, all columns in the result are selected. Use Ctrl-click to change the selection. If you want only a few columns out of many, it is easier to specify the column names in the SELECT clause of the query.
5. Click Save Table to add the derived table to Available objects. A distinctive icon, identifies it as a derived table.