Multi-field Embedded Equijoins

In relational systems, a relationship or link between tables can depend on multiple columns. Embedded joins defined in multi-table Master and Access Files also provide this ability. (The dynamic JOIN command supports this feature as well. See Advanced Reporting Techniques).

To describe a multi-field join for a multi-table structure, specify multiple field names for the KEYFLD and IXFLD attributes in the Access File. Separate the component fields participating in the multi-field join with slash (/) symbols.

Syntax: How to Implement a Multi-Field Embedded Equijoin

SEGNAME=name1, TABLENAME=table1,...,$
SEGNAME=name2, TABLENAME=table2,...,
   KEYFLD=pkfield1/pkfield2,
   IXFLD=fkfield1/fkfield2,$

where:

name1

Is the SEGNAME of the parent table from the multi-table Master File.

name2

Is the SEGNAME of the related table from the multi-table Master File.

table1, table2

Are the names of the parent (table1) and related (table2) tables:

For DB2, tablename or creator.tablename. For the DB2 Distributed Data Facility, also include a subsystem location identifier (see Additional Topics).
For Teradata, tablename or databasename.tablename.
For IDMS/SQL, tablename or schema.tablename.
For Oracle, userid.tablename.

pkfield1 / pkfield2: Are the field names that compose the primary key in the parent (or host) table.
fkfield1 / fkfield2: Are the field names that compose the foreign key in the related table.

Up to 16 fields can participate in a link between two tables. The fields that constitute this multi-field relationship do not have to be contiguous either within the table or the FOCUS Master File.

The number and order of fields for the KEYFLD value must correspond to those for the IXFLD value.

If the list of fields exceeds one line (80 characters), continue it on a second line. You can use as many lines as necessary, provided that each line is filled up to and including the 80th position. The 80th position cannot contain a slash (/) character.

Top of page

Example: Creating a Multi-Field Embedded Equijoin

To illustrate the multi-field equijoin, suppose that the fields LAST_NAME and FIRST_NAME compose the primary key for the EMPINFO1 table. Also, assume that fields the LNAME and FNAME serve as the common fields (foreign key) in the COURSE1 table.

The ECOURSE1 Master File (defined solely for the purpose of this example and not included in File Descriptions and Tables) reflects the new fields:

FILENAME=ECOURSE1         ,SUFFIX=SQLDS,$
SEGNAME=EMPINFO1          ,SEGTYPE=S0,$
 FIELDNAME=LAST_NAME      ,ALIAS=LN             ,USAGE=A15  ,ACTUAL=A15,$
 FIELDNAME=FIRST_NAME     ,ALIAS=FN             ,USAGE=A10  ,ACTUAL=A10,$
 FIELDNAME=HIRE_DATE      ,ALIAS=HDT            ,USAGE=YMD  
,ACTUAL=DATE,$
 FIELDNAME=DEPARTMENT_CD  ,ALIAS=DEPARTMENT_CD  ,USAGE=A10  ,ACTUAL=A10,
   MISSING=ON ,$
 FIELDNAME=CURRENT_SALARY ,ALIAS=CURRENT_SALARY ,USAGE=P9.2 ,ACTUAL=P4, $
 FIELDNAME=CURR_JOBCODE   ,ALIAS=CJC            ,USAGE=A3   ,ACTUAL=A3,$
 FIELDNAME=ED_HRS         ,ALIAS=OJT            ,USAGE=F6.2 ,ACTUAL=F4,
   MISSING=ON ,$
 FIELDNAME=BONUS_PLAN     ,ALIAS=BONUS_PLAN     ,USAGE=I4   ,ACTUAL=I4,$
SEGNAME=COURSE1           ,SEGTYPE=S0           ,PARENT=EMPINFO1, $
 FIELD=CNAME              ,ALIAS=COURSE_NAME    ,USAGE=A15  ,ACTUAL=A15,$
 FIELD=LNAME              ,ALIAS=LNAME          ,USAGE=A15  ,ACTUAL=A15,$
 FIELD=FNAME              ,ALIAS=FNAME          ,USAGE=A10  ,ACTUAL=A10,$
 FIELD=GRADE              ,ALIAS=GRADE          ,USAGE=A1   ,ACTUAL=A1,
   MISSING=ON ,$
 FIELD=YR_TAKEN           ,ALIAS=YR_TAKEN       ,USAGE=A2   ,ACTUAL=A2, $
 FIELD=QTR                ,ALIAS=QUARTER        ,USAGE=A1   ,ACTUAL=A1, $

In the ECOURSE1 Access File, the KEYFLD and IXFLD values consist of fieldnames separated by a slash (/):

SEGNAME = EMPINFO1 ,TABLENAME = "USER1"."EMPINFO1" ,KEYS = 2,WRITE = YES,
   DBSPACE = PUBLIC.SPACE0,$
SEGNAME = COURSE1  ,TABLENAME = "USER1"."COURSE1"  ,KEYS = 3,WRITE = YES,
   DBSPACE = PUBLIC.SPACE0,
      KEYFLD=LAST_NAME/FIRST_NAME,IXFLD=LNAME/FNAME, $