Conditional Joins in the Master File

How to:

The conditional (or WHERE-based) join describes how to relate rows from two data sources based on any condition. In this type of embedded join, the Master File for one data source contains a cross-reference to the Master File for the other data source. When used to relate non-FOCUS data sources, a conditional embedded join does not require a multi-table Access File.

Syntax: How to How to Define a Conditional Join in the Master File

The conditions specified in the join are considered virtual fields in the Master File. You can use the CRJOINTYPE attribute to specify the type of join. 

FILENAME=filename, SUFFIX=suffix   [,$]
 SEGNAME=file1, SEGTYPE= {S0|KL} [,CRFILE=crfile1] [,$]
  FIELD=name1,...,$
  .
  .
  .
SEGNAME=seg,  SEGTYPE=styp,  PARENT=parseg,
        CRFILE=xmfd,  [CRSEG=xseg,  ], [CRJOINTYPE = {INNER|LEFT_OUTER}]
        JOIN_WHERE=expression; [JOIN_WHERE=expression; ...] ,$

where:

filename

Is the name of the Master File.

suffix

Is the SUFFIX value.

file1

Is the SEGNAME value for the parent segment.

name

Is any field name.

seg

Is the segment name for the joined segment. Only this segment participates in the join, even if the cross-referenced Master File describes multiple segments.

styp

Is the segment type for the joined segment. Can be DKU, DKM, KU, or KM, as with traditional cross-references in the Master File.

Note: If you specify a unique join when the relationship between the host and cross-referenced files is one-to-many, the results will be unpredictable.

parseg

Is the parent segment name.

xmfd

Is the cross-referenced Master File.

xseg

Is the cross-referenced segment, if seg is not the same name as the SEGNAME in the cross-referenced Master File.

expression

Is any expression valid in a DEFINE FILE command. All of the fields referenced in all of the expressions must lie on a single path.

Example: Using a Conditional Join in the Master File

The following Master File named EMPDATAJ1 defines a conditional join between the EMPDATA and JOBHIST data sources. 

FILENAME=EMPDATA, SUFFIX=FOC  , DATASET=ibisamp/empdata.foc 
SEGNAME=EMPDATA,  SEGTYPE=S1      
 FIELDNAME=PIN,          ALIAS=ID,       FORMAT=A9,  INDEX=I,    $  
 FIELDNAME=LASTNAME,     ALIAS=LN,       FORMAT=A15,             $   
 FIELDNAME=FIRSTNAME,    ALIAS=FN,       FORMAT=A10,             $ 
 FIELDNAME=MIDINITIAL,   ALIAS=MI,       FORMAT=A1,              $ 
 FIELDNAME=DIV,          ALIAS=CDIV,     FORMAT=A4,              $  
 FIELDNAME=DEPT,         ALIAS=CDEPT,    FORMAT=A20,             $  
 FIELDNAME=JOBCLASS,     ALIAS=CJCLAS,   FORMAT=A8,              $ 
 FIELDNAME=TITLE,        ALIAS=CFUNC,    FORMAT=A20,             $  
 FIELDNAME=SALARY,       ALIAS=CSAL,     FORMAT=D12.2M,          $ 
 FIELDNAME=HIREDATE,     ALIAS=HDAT,     FORMAT=YMD,             $   
SEGNAME=JOBHIST, PARENT=EMPDATA,  SEGTYPE=DKM, CRFILE=ibisamp/jobhist, CRJOINTYPE=INNER,$
   JOIN_WHERE = EMPDATA.JOBCLASS CONTAINS '257' AND JOBHIST.JOBCLASS CONTAINS '019';$

The following request uses the joined Master File. 

TABLE FILE EMPDATAJ1
SUM SALARY TITLE AS 'Empdata Title' FUNCTITLE AS 'Jobhist Title'
BY LASTNAME 
BY FIRSTNAME 
BY EMPDATA.JOBCLASS AS 'Empdata Job'
BY JOBHIST.JOBCLASS AS 'Jobhist Job'
WHERE LASTNAME LT 'D'
ON TABLE SET PAGE NOPAGE
          
ON TABLE SET STYLE *
GRID=OFF,$
FONT=ARIAL, SIZE=8,$
TYPE=TITLE, STYLE=BOLD,$
END

The following image shows that all of the job class values from the EMPDATA segment start with the characters 257, and all of the job class values from the JOBHIST segment start with the characters 019, as specified in the join condition: