A script-enabled browser is required for this page to function properly.

JOIN of Multiple Tables

Prior to Oracle Forms 6i, Forms supported the query data sources of type "Table" based on a single table. Now, Forms supports table query data sources based on a JOIN of multiple tables. A JOIN of multiple tables is not appropriate for a DML data target, so the data target must still be based on a single table.

Follow the steps described in the Task Overview to create a block with a query data source based on a join of multiple tables and a DML data target based on a single table. See the Example to create an EMP block with a query data source based on a join of the EMP and DEPT tables and a DML data target based on the EMP table.

Task Overview

  1. Using the Data Block Wizard, create a block based on one of the tables in the join.
    Often in a join, one of the tables is the "driving table" which is joined to several other foreign key lookup tables. The Data Block Wizard helps create data items from table columns, so it is recommended that you choose the table that contains the most columns to be included in your block, which is usually the driving table. After using the Data Block Wizard, the block is created.
  2. Select the block, and set the Query Data Source Name property to the list of tables in the join.
    The list of tables should be separated with commas, as they would appear in the FROM clause of the join SELECT statement.
  3. Set the WHERE clause to contain the join condition between the tables in the join.
  4. Set the DML Data Target Name property to the appropriate table for inserts, updates, and deletes. This is usually the driving table.
  5. Manually create new items for columns in the other tables in the join that you want to include in the block.
  6. Select each of the items created in step 5, and set the Database Item property to Yes.
  7. Select each of the items created in step 5, and set the Column Name property to the column name in the table.
  8. Select each of the items created in step 5, and set the Query Only property to Yes.
    This setting prevents the item from being included in inserts and updates to the DML Data Target table.
  9. For all items where the column name is in more than one table in the join, qualify the column name with the table name in the Column Name property (for example table.column).
  10. For all items that are the primary key of the DML Data Target table, set the Primary Key property to Yes.

Example Using EMP and DEPT

This example uses the EMP and DEPT sample tables. It creates an EMP block which displays the employee information and the name of department instead of the department number. The EMP table is the driving table which is joined to the DEPT table to lookup the department name.

  1. Create a block based on the EMP table using the Data Block Wizard.
  2. Using the Property Palette, select the EMP block and change the Query Data Source Name from EMP to EMP, DEPT.
  3. Set the WHERE clause to EMP.DEPTNO = DEPT.DEPTNO.
  4. Set the DML Data Target Name to EMP.
  5. Manually create new items that correspond to columns in the DEPT table. For example, create "DNAME".
  6. Select the DNAME item and set Database Item to Yes.
  7. Set the Column Name to DEPT.DNAME.
  8. Set Query Only to Yes. This will prevent the DNAME item from being included in updates to the EMP table.
  9. Qualify the column name of any items that are columns in both the EMP and DEPT tables. For example, change the Column Name of the DEPTNO item from DEPTNO to EMP.DEPTNO. This will prevent an ambiguous column name error during query.
  10. Specify one of the items as the primary key. For example, set Primary Key to Yes on the "Empno" item. This is required because the query data source and DML targets are different.