Creating Keys For JOIN

Creating Keys For JOIN

By Steve Simon

The Problem

While working at Air Canada a few years back, I encountered problem where one of our databases contained flight information with a start date of the service and a planned termination date for the “validity of the service”. (See below)

Figure 1

Our booking database (by class See Figure 5) on the other hand contained daily records of the status of each class for each flight segment.

This necessitated the break down of the data shown in Figure 1 to “a record per day” See Figure 2 below.

Figure 2

IT SHOULD BE NOTED THAT EACH OF THE “Origin-Destination” pairs  in the whole file that Figure 1 is a part of,  had DIFFERENT START AND END DATES of validity for each of the “Origin-Destination” pairs.

The Solution

Utilizing the data shown in Figure 1, The data is Fixformed into a focus database.

$==================================================================$     
$    HOLD file created on 2006/04/21 at 10.46.09 by FOCUS 4.3      $     
$              Database records retrieved=       62                $     
$              Records in the HOLD file  =       62                $     
$==================================================================$     
FILE=AIRLINE        ,SUFFIX=FOC                                          
SEGNAME=SEG01   ,SEGTYPE=S2                                              
FIELDNAME   =DEPARTURE          ,ALIAS= E01 ,FORMAT=A3 ,FIELDTYPE = I, $ 
FIELDNAME   =ARRIVAL            ,ALIAS= E03 ,FORMAT=A3 ,FIELDTYPE = I, $ 
FIELDNAME   =DEPARTURECITY      ,ALIAS= E02 ,FORMAT=A50, $               
FIELDNAME   =ARRIVALCITY        ,ALIAS= E04 ,FORMAT=A50, $               
SEGNAME=SEG02   ,PARENT =SEG01, SEGTYPE = U                              
FIELDNAME = STARTDATE ,ALIAS = E05,FORMAT = YYMD, $                      
FIELDNAME = ENDDATE   ,ALIAS = E06,FORMAT = YYMD, $  

Another modify will create an individual record for each city pair independent of the start and end date for each city pair.

Create the database and Load

FILEDEF RAW DISK C:/ibi/apps/steve/AirlineSchedule.txt
FILEDEF AIRLINE DISK C:\ibi\apps\steve\AIRLINE.FOC(LRECL 4096  RECFM F
-RUN
CREATE FILE AIRLINE
-RUN
-*
MODIFY FILE AIRLINE
FIXFORM DEPARTURE/3 DEPARTURECITY/50 ARRIVAL/3 ARRIVALCITY/50
FIXFORM STARTDATE/A8 ENDDATE/A8
MATCH WITH-UNIQUES DEPARTURE ARRIVAL
ON MATCH REJECT
ON NOMATCH INCLUDE
DATA ON RAW
END

Create a single record.

FILEDEF AIRLINE1 DISK C:/ibi/apps/steve/AIRLINE.OUTTT
 -RUN
 MODIFY FILE AIRLINE
 PERFORM EXTRACT1
 COMPUTE STARTDATE1/YYMD = 0;
 COMPUTE ENDDATE1/YYMD = 0;
 COMPUTE STARTCITY/A50=;
 COMPUTE ENDCITY/A50=;
 COMPUTE STARTCODE/A3=;
 COMPUTE ENDCODE/A3=;
 COMPUTE TEMPDATE/YYMD=0;
 -*
 CASE EXTRACT1
      NEXT WITH-UNIQUES DEPARTURE ARRIVAL
           ON NEXT ACTIVATE DEPARTURECITY ARRIVALCITY STARTDATE ENDDATE
           ON NEXT COMPUTE STARTDATE1= D.STARTDATE;
           ON NEXT COMPUTE ENDDATE1  = D.ENDDATE;
           ON NEXT COMPUTE STARTCITY = D.DEPARTURECITY;
           ON NEXT COMPUTE ENDCITY   = D.ARRIVALCITY;
           ON NEXT COMPUTE STARTCODE = D.DEPARTURE;
           ON NEXT COMPUTE ENDCODE   = D.ARRIVAL;
           ON NEXT COMPUTE TEMPDATE  = D.STARTDATE;
           ON NEXT PERFORM EXTRACT2
           ON NONEXT GOTO EXIT
 ENDCASE
 -*
 CASE EXTRACT2
 IF TEMPDATE GT ENDDATE1 THEN PERFORM EXTRACT1;
 -*IF TEMPDATE GT ENDDATE1 THEN GOTO EXIT;
 TYPE ON AIRLINE1 ""
 COMPUTE TEMPDATE = TEMPDATE + 1;
 GOTO EXTRACT2
 ENDCASE
 DATA
 END
 -RUN

And this produces:
Figure 3

We then create a key of the date with the city pairs:
Figure 4

And with this key we are able to join to our daily bookings file seen below:
Figure 5

Any questions, please email Steve at okeofs1@cox.net.