|
How to: |
In this implementation of the sliding window technique, you change the metadata used by an application. Two pairs of Master File attributes enable you to define a window on a file or field level:
A file-level window takes precedence over a global window for the dates associated with that file.
A field-level window takes precedence over a file-level or global window for the dates associated with that field.
For details on Master Files, see the Describing Data manual.
To define a window that applies to all legacy date fields in a file, add the FDEFCENT and FYRTHRESH attributes to the Master File on the file declaration.
The syntax for the first attribute is
{FDEFCENT|FDFC} = {cc|19}where:
Is the century for the start date of the window. If you do not supply a value, cc defaults to 19, for the twentieth century.
The syntax for the second attribute is
{FYRTHRESH|FYRT} = {[-]yy|0}
where:
Is the year threshold for the window. If you do not supply a value, yy defaults to zero (0).
If yy is a positive number, two-digit years greater than or equal to the threshold default to the value of DEFCENT for the century. Two-digit years less than the threshold assume the value of DEFCENT + 1.
If yy is a negative number (-yy), the start date of the window is derived by subtracting that number from the current year, and DEFCENT is automatically calculated. The start date is automatically incremented by one at the beginning of each successive year.
Tip: Use the abbreviated forms of FDEFCENT/FYRTHRESH or DEFCENT/YRTHRESH to reduce keystrokes. The examples in this topic use the abbreviated forms where available (for instance, FDFC instead of FDEFCENT). Maintain supports only the abbreviated forms in certain command syntax (for example, on a COMPUTE or DECLARE command). For details, see the Maintaining Databases manual.
In the following example, the FDEFCENT and FYRTHRESH attributes define a window from 1982 to 2081. The window is applied to all legacy date fields in the file, including HIRE_DATE, DAT_INC, and others, if they are converted to a date format.
The Master File is:
FILENAME=EMPLOYEE, SUFFIX=FOC, FDFC=19, FYRT=82 SEGNAME=EMPINFO, SEGTYPE=S1 FIELDNAME=EMP_ID, ALIAS=EID, FORMAT=A9, $ FIELDNAME=LAST_NAME, ALIAS=LN, FORMAT=A15, $ FIELDNAME=FIRST_NAME, ALIAS=FN, FORMAT=A10, $ FIELDNAME=HIRE_DATE, ALIAS=HDT, FORMAT=I6YMD, $ . . . FIELDNAME=DAT_INC, ALIAS=DI, FORMAT=I6YMD, $ . . .
The DEFINE command in the following request creates two virtual fields named NEW_HIRE_DATE, which is derived from the existing field HIRE_DATE; and NEW_DAT_INC, which is derived from DAT_INC. The format of HIRE_DATE and DAT_INC is I6YMD, which is a legacy date with a 2-digit year. NEW_HIRE_DATE and NEW_DAT_INC are date formats with 4-digit years (YYMD). For details on DEFINE, see the Creating Reports manual.
DEFINE FILE EMPLOYEE NEW_HIRE_DATE/YYMD = HIRE_DATE; NEW_DAT_INC/YYMD = DAT_INC; END TABLE FILE EMPLOYEE PRINT HIRE_DATE NEW_HIRE_DATE DAT_INC NEW_DAT_INC END
The window created in the Master File applies to both legacy date fields. In the report, the year 82 (which is equal to the threshold), for both HIRE_DATE and DAT_INC, defaults to the century value 19 and is returned as 1982 in the NEW_HIRE_DATE and NEW_DAT_INC columns. The year 81, for both HIRE_DATE and DAT_INC, is less than the threshold 82 and assumes the century value 20 (FDEFCENT + 1).
The partial output is:
PAGE 1 HIRE_DATE NEW_HIRE_DATE DAT_INC NEW_DAT_INC --------- ------------- ------- ----------- 80/06/02 2080/06/02 82/01/01 1982/01/01 80/06/02 2080/06/02 81/01/01 2081/01/01 81/07/01 2081/07/01 82/01/01 1982/01/01 82/05/01 1982/05/01 82/06/01 1982/06/01 82/05/01 1982/05/01 82/05/01 1982/05/01 . . .
To define a window that applies to a specific legacy date field, add the DEFCENT and YRTHRESH attributes to the Master File on the field declaration.
The syntax for the first attribute is
{DEFCENT|DFC} = {cc|19}where:
Is the century for the start date of the window. If you do not supply a value, cc defaults to 19, for the twentieth century.
The syntax for the second attribute is
{YRTHRESH|YRT} = {[-]yy|0}
where:
Is the year threshold for the window. If you do not supply a value, yy defaults to zero (0).
If yy is a positive number, two-digit years greater than or equal to the threshold default to the value of DEFCENT for the century. Two-digit years less than the threshold assume the value of DEFCENT + 1.
If yy is a negative number (-yy), the start date of the window is derived by subtracting that number from the current year, and DEFCENT is automatically calculated. The start date is automatically incremented by one at the beginning of each successive year.
In this example, the application requires a different window for two legacy date fields in the same file.
The DEFCENT and YRTHRESH attributes in the Master File define a window for HIRE_DATE from 1982 to 2081, and a window for DAT_INC from 1983 to 2082.
The Master File is:
FILENAME=EMPLOYEE, SUFFIX=FOC SEGNAME=EMPINFO, SEGTYPE=S1 FIELDNAME=EMP_ID, ALIAS=EID, FORMAT=A9, $ FIELDNAME=LAST_NAME, ALIAS=LN, FORMAT=A15, $ FIELDNAME=FIRST_NAME, ALIAS=FN, FORMAT=A10, $ FIELDNAME=HIRE_DATE, ALIAS=HDT, FORMAT=I6YMD, DFC=19, YRT=82, $ . . . FIELDNAME=DAT_INC, ALIAS=DI, FORMAT=I6YMD, DFC=19, YRT=83, $ . . .
The request is the same one used in the previous example (defining a file-level window in a Master File):
DEFINE FILE EMPLOYEE NEW_HIRE_DATE/YYMD = HIRE_DATE; NEW_DAT_INC/YYMD = DAT_INC; END TABLE FILE EMPLOYEE PRINT HIRE_DATE NEW_HIRE_DATE DAT_INC NEW_DAT_INC END
However, the report illustrates the use of two different windows for the two legacy date fields. For example, the year 82 for HIRE_DATE defaults to the century value 19, since 82 is equal to the threshold for the window for this field. The date returned for NEW_HIRE_DATE is 1982.
The year 82 for DAT_INC assumes the century value 20 (DEFCENT + 1), since 82 is less than the threshold for the window for this field (83). The date returned for NEW_DAT_INC is 2082.
The partial output is:
PAGE 1 HIRE_DATE NEW_HIRE_DATE DAT_INC NEW_DAT_INC --------- ------------- ------- ----------- 80/06/02 2080/06/02 82/01/01 2082/01/01 80/06/02 2080/06/02 81/01/01 2081/01/01 81/07/01 2081/07/01 82/01/01 2082/01/01 82/05/01 1982/05/01 82/06/01 2082/06/01 82/05/01 1982/05/01 82/05/01 2082/05/01 . .
This example illustrates the use of field-level DEFCENT and YRTHRESH attributes to define a window used with MODIFY. To run this example yourself, you need to create a Master File named DATE and a procedure named DATELOAD.
The Master File describes a segment with 12 date fields of different formats. The first field is a date format field. The DEFCENT and YRTHRESH attributes included on this field create a window from 1990 to 2089. The window is required because the input data for the first date field does not contain century digits, and the default value 19 cannot be assumed.
The Master File looks like this:
FILENAME=DATE, SUFFIX=FOC SEGNAME=ONE, SEGTYPE=S1 FIELDNAME=D1_YYMD, ALIAS=D1, FORMAT=YYMD, DFC=19, YRT=90, $ FIELDNAME=D2_I6YMD, ALIAS=D2, FORMAT=I6YMD, $ FIELDNAME=D3_I8YYMD, ALIAS=D3, FORMAT=I8, $ FIELDNAME=D4_A6YMD, ALIAS=D4, FORMAT=A6YMD, $ FIELDNAME=D5_A8YYMD, ALIAS=D5, FORMAT=A8YYMD, $ FIELDNAME=D6_I4YM, ALIAS=D6, FORMAT=I4YM, $ FIELDNAME=D7_YQ, ALIAS=D7, FORMAT=YQ, $ FIELDNAME=D8_YM, ALIAS=D8, FORMAT=YM, $ FIELDNAME=D9_JUL, ALIAS=D9, FORMAT=JUL, $ FIELDNAME=D10_Y, ALIAS=D10, FORMAT=Y, $ FIELDNAME=D11_YY, ALIAS=D11, FORMAT=YY, $ FIELDNAME=D12_MDYY, ALIAS=D12, FORMAT=MDYY, $
The procedure (DATELOAD) creates a FOCUS data source named DATE and loads two records into it. The first field of the first record contains the 2-digit year 92. The first field of the second record contains the 2-digit year 88. For details on commands such as CREATE and MODIFY, and others used in this file, see the Maintaining Databases manual.
The procedure looks like this:
CREATE FILE DATE MODIFY FILE DATE FIXFORM D1/8 D2/6 D3/8 D4/6 D5/8 D6/4 D7/4 D8/4 D9/5 D10/2 D11/4 D12/8 MATCH D1 ON NOMATCH INCLUDE ON MATCH REJECT DATA 92022900022920000229000229200002290002000100020006000200002292000 88022900022920000229000229200002290002000100020006000200002292000 END
The following request accesses all the fields in the new data source:
TABLE FILE DATE PRINT * END
In the report, the year 92 for D1_YYMD defaults to the century value 19, since 92 is greater than the threshold for the window for this field (90). It is returned as 1992 in the D1_YYMD column. The year 88 assumes the century value 20 (DEFCENT + 1), because 88 is less than the threshold. It is returned as 2088 in the D1_YYMD column.
The partial output is:
PAGE 1 D1_YYMD D2_I6YMD D3_I8YYMD D4_A6YMD D5_A8YYMD D6_I4YM D7_YQ D8_YM ... ------- -------- --------- -------- --------- ------- ----- ----- 1992/02/29 00/02/29 20000229 00/02/29 2000/02/29 00/02 00 Q1 00/02 ... 2088/02/29 00/02/29 20000229 00/02/29 2000/02/29 00/02 00 Q1 00/02 ...
The following Master File defines windows at both the file and field level:
FILENAME=EMPLOYEE, SUFFIX=FOC, FDFC=19, FYRT=83 SEGNAME=EMPINFO, SEGTYPE=S1 FIELDNAME=EMP_ID, ALIAS=EID, FORMAT=A9, $ FIELDNAME=LAST_NAME, ALIAS=LN, FORMAT=A15, $ FIELDNAME=FIRST_NAME, ALIAS=FN, FORMAT=A10, $ FIELDNAME=HIRE_DATE, ALIAS=HDT, FORMAT=I6YMD, DFC=19, YRT=82, $ . . . FIELDNAME=EFFECT_DATE, ALIAS=EDATE, FORMAT=I6YMD, $ . . . FIELDNAME=DAT_INC, ALIAS=DI, FORMAT=I6YMD, $ . . .
The request is:
DEFINE FILE EMPLOYEE NEW_HIRE_DATE/YYMD = HIRE_DATE; NEW_EFFECT_DATE/YYMD = EFFECT_DATE; NEW_DAT_INC/YYMD = DAT_INC; END TABLE FILE EMPLOYEE PRINT HIRE_DATE NEW_HIRE_DATE EFFECT_DATE NEW_EFFECT_DATE DAT_INC NEW_DAT_INC END
When the field HIRE_DATE is accessed, the time span 1982 to 2081 is applied. For all other legacy date fields in the file, such as EFFECT_DATE and DAT_INC, the time span specified at the file level is applied, that is, 1983 to 2082.
For example, the year 82 for HIRE_DATE is returned as 1982 in the NEW_HIRE_DATE column, since 82 is equal to the threshold of the window for that particular field. The year 82 for EFFECT_DATE and DAT_INC is returned as 2082 in the columns NEW_EFFECT_DATE and NEW_DAT_INC, since 82 is less than the threshold of the file-level window (83).
The partial output is:
PAGE 1 HIRE_DATE NEW_HIRE_DATE EFFECT_DATE NEW_EFFECT_DATE DAT_INC NEW_DAT_INC --------- ------------- ----------- --------------- ------- ---------- 80/06/02 2080/06/02 82/01/01 2082/01/01 80/06/02 2080/06/02 81/01/01 2081/01/01 81/07/01 2081/07/01 82/01/01 2082/01/01 82/05/01 1982/05/01 82/11/01 2082/11/01 82/06/01 2082/06/01 82/05/01 1982/05/01 82/11/01 2082/11/01 82/05/01 2082/05/01 .
Missing date values for NEW_EFFECT_DATE appear as blanks by default. To retrieve the base date value for NEW_EFFECT_DATE instead of blanks, issue the command
SET DATEDISPLAY = ON
before running the request. The base date value is returned as 1900/12/31. See Defining a Global Window With SET for sample results.
|
|
Information Builders |