Derived tables consist of a set of SQL statements that you create in the data foundation and use as a logical table to create objects in the business layer.
Derived tables are similar to views in the database, but because they are defined in the data foundation, they give designers more flexibility. A derived table can be considered a query that can be referenced as a table. You insert the table definition SQL into an end-user query at runtime.
Note
Note that the same performance issues that affect queries can also affect derived tables, as they are virtual, not physical, tables.
Derived tables can be used to merge data together from different tables, when designers are unable to conform the underlying data source, and want to normalize or renormalize the business layer schema.
Use derived tables in the following situations:
To create a table with columns from other tables. The column definitions can include complex calculations and functions.
To create a single table that combines two or more tables (called merged tables).
To insert a derived table with all the columns in the original table, right-click the table header in the data foundation view and select Insert → Derived Table.
To insert a derived table and specify the columns, select Insert Derived Table from the Insert menu in the data foundation view.
Enter the table definition in the Edit Derived Table dialog box.
Give the derived table a unique name within the data foundation. From the tables listed in the Tables and Database Table boxes, drag columns to the Expression box to include in the derived table.
You can also use the SQL Builder, which works like the query panel, to select columns for the derived table. The functions allowed in the expression definition are listed in the Functions box. You can drag functions to the Expression box. To edit a derived table, right-click the table header of the derived table and select Edit.
