Joined Tables

Alternatively, you can access data from multiple tables using the in TABLE(COLUMN) clause when defining a type. For more information, see Specifying Data from Multiple Tables.

Use the following syntax to create a joined table:

#table VIRTUALTABLENAME
#join TABLE,TABLE,TABLE...
#where EXPRESSION

The where statements describe the association between table columns using Oracle or SQL expressions. Note that EXPRESSION can use operators with TABLE.COLUMN syntax when referring to values in a column.

In the human resources example, a virtual table that defines an employee’s salary grade for each employee based on the data in the EMP and SALGRADE tables is described as follows:

table EMPGRADE {
  join EMP,SALGRADE;
  where "EMP.SAL >= SALGRADE.LOSAL and EMP.SAL <= SALGRADE.HISAL";
}

Joined tables are read-only and cannot be updated when you design the mapping file.

The join only includes rows in table1 that have a row in table2 where the “col1” matches. If the data you want to load from multiple tables does not exhibit the 1-1 relationship required for the above join to work, you need to create a database view with an “outer join” of the tables involved. The business/relationship type should then be mapped to this view.

The “using” clause syntax is not allowed when running the SQL Loader. A mapping file that looks like the one below will cause an error when loading data using the SQL Loader. Note the use of the “using” clause to fetch the description for the type “Employee”.

table EMPLOYEE {  EMPNO string;

  FIRSTNME string;
  MIDINIT string;
  LASTNAME string;
  WORKDEPT string;
  PHONENO string;
  HIREDATE date;
  JOB string;
  EDLEVEL int;
  SEX string;
  BIRTHDATE date;
  SALARY real;
  BONUS real;
  COMM real;
}

table EMP_RESUME {
  EMPNO string;
  RESUME_FORMAT string;
  RESUME long;
}

type Employee {
 id EMPLOYEE(EMPNO) mapped;
   name LASTNAME;
  revision FIRSTNME;
  description EMP_RESUME(RESUME) using EMP_RESUME(EMPNO);
}