Creating and Using Derived Tables


After completing this lesson, you will be able to:

  • Create and Use Derived Tables

Derived Tables

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 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 InsertDerived 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.

Derived Tables by Merging Tables

Merging tables lets you insert a derived table into the data foundation comprised of the combined columns from two or more tables, linked by joins. To merge tables, in the data foundation view, select the tables you want to merge in one of the following ways:

  • Right-click a table and select Related Tables.

  • Click the table headers while holding down the CTRL key. Right-click the selection of tables and then select Merge.

Enter a name for the table that is unique within the data foundation. You have now inserted the merged table as a derived table. The new table is joined to any of the tables that the original tables were joined to. The original tables are now obsolete. You can delete these tables if you wish. If you choose to keep the original tables, the joins linking those tables are deleted. The tables stay in the data foundation, however. To edit a merged table, right-click the table header, and select Edit Derived Table.

Create a Use a Derived Table on the Data Foundation

Into your data foundation, insert a derived table to show the number of transactions per customer.

Log in to track your progress & complete quizzes