Adding a derived table

Derived tables are not physical ERP tables although they can be used in the same way. Derived tables use an SQL script to aggregate data from multiple ERP tables and are designed to make it easier for you to obtain information from multiple tables. Several tables with specific columns selected from those tables may be joined to create a new table that combines the required data into a derived table.

You would use derived tables for the following reasons:

  • They are time savers and aid in mapping as all the required fields are available in a single table

  • They allow you to aggregate data from different ERP tables
  • You can use the same derived table to map multiple destination columns

When you click on a derived table from the left panel, the right panel displays the columns of the derived table and the SQL statement that directs which table the column should come from.

To add a derived table, click Add and provide the following details:

  1. Provide a unique name for the table.
  2. Provide a description for the table.
  3. Save the table. It is automatically prefixed with DT_indicating that it is a derived table. Once you save the table, you can add columns.
  4. In the Derived Columns panel, right click to add a new column. Alternatively click Add to add a new column.
  5. Provide the column name and the type of data the column should contain, for example, integers or decimal numbers or date and time. After adding each individual column, the newly added column will get reflected in the SQL Select statement panel.
  6. Provide the SQL code that directs which table the column should come from. The column cannot be saved unless the SQL code is added. After adding each individual column, the newly added column will get reflected in the SQL Statements panel. The SQL Statements field consists of:
    • Select Statement: This field is populated when you provide the SQL code. The statement refers to the created column.
    • From Statement: This must be explicitly specified and defines the table from which the values will be fetched.
  7. From the tool bar at the top of the page, click Project > Save.
  8. If you are working with an Oracle or PeopleSoft project, click the Test button  to physically execute the SQL statement against the connection. This button is not applicable for Lawson and SAP projects.
    Note: For Lawson and SAP projects, you can copy your SQL queries to a clipboard and paste  them in Notepad to test your query in the database.

    To edit a derived table, click Edit and to remove a derived table click Remove.