The Department Table (TBLDEPT) contains only two fields: Department Code (DEPT) which is the key field, and Department Name (DEPTNAME) which is the translation as defined in the PIMS manual. This table is allocated whenever any other file is selected for ad hoc reporting.
In FOCUS databases (e.g., AC) the Department Table is automatically joined to the host file via the key field. However, in external files such as EH, a match is required to merge the data in the files. The sample program below illustrates a match between the Employment History Extract (EH) file and the Department Table (TBLDEPT).
EX EH
MATCH FILE EH
PRINT eh:wname
BY EH:DEPT
RUN
FILE TBLDEPT
PRINT DEPTNAME
BY DEPT AS EH:DEPT
AFTER MATCH HOLD AS PERMSML OLD
END
The resulting hold file, PERMSML, requires a define to fill-in blank values for department name:
DEFINE FILE PERMSML ADD
DPTNAME/A30 =
IF (EH:DEPT EQ LAST EH:DEPT ) AND (DEPTNAME EQ ' ') AND
(LAST DEPTNAME NE ' ') THEN LAST DEPTNAME
ELSE
IF (EH:DEPT EQ LAST EH:DEPT ) AND (DEPTNAME EQ ' ') AND
(LAST DEPTNAME EQ ' ') THEN LAST DPTNAME
ELSE DEPTNAME;
END
The final report is generated per the commands below:
TABLE FILE PERMSML
PRINT eh:wname dptNAME
BY EH:DEPT
END