How to: Reference: |
The OCCURS attribute in a Master File describes repeating fields or groups of fields in a non-FOCUS data source. The repeating group of fields is described as a descendent segment in the Master File, and the OCCURS attribute for that segment specifies how to determine the number of repetitions.
The number of repetitions does not have to be the same for every record instance. Sometimes, the number of repetitions can be derived from a field in the data source. In that case, you can create a virtual field in the Master File that indicates the number of repetitions for each record and use that virtual field as the value of the OCCURS attribute.
SEGNAME = parent, SEGTYPE = segtype,$ . . . DEFINE definefield/In = expression; SEGNAME = osegname, SEGTYPE=S0, PARENT = parent, OCCURS = definefield ,$ FIELDNAME = rfield, ALIAS = ralias, USAGE = rufmt, ACTUAL = rafmt,$ . . . [FIELDNAME = orderfield, ALIAS = ORDER, USAGE = In, ACTUAL = I4,$]
where:
Is the name of the parent segment.
Is the SEGTYPE of the parent segment.
Is the virtual field that indicates the number of repetitions of the repeating field or group of fields. This field must be defined in a segment that is an ancestor of the segment containing the repeating fields.
Is the format of the virtual field that describes the number of repetitions. It must be an integer format.
Is a valid expression that derives the number of repetitions for each record instance.
Is the name of the descendent OCCURS segment.
Is the name of a repeating field in the OCCURS segment.
Is the alias of a repeating field in the OCCURS segment.
Is the display format for a repeating field in the OCCURS segment.
Is the actual format for a repeating field in the OCCURS segment.
Is the name of an internal counter field that you can specify as the last field in the OCCURS segment. The ORDER field associates a sequence number with each occurrence and is useful when the order of the repeating data is significant. For example, the values may represent monthly or quarterly data, but the record itself may not explicitly specify the month or quarter to which the data applies. The USAGE format must be integer and the ACTUAL format is I4.
The virtual field used as the OCCURS value cannot be redefined inside or outside of the Master File.
The following request against the EMPLOYEE data source creates a fixed-format sequential file with a repeating field. The request:
Note that the number of deductions will vary for each employee. The part of the record that contains the deductions will constitute the OCCURS segment. The number of repetitions will have to be derived from the length field created in the TABLE request.
The procedure to create the file with the repeating deduction field follows:
DEFINE FILE EMPLOYEE CTR/I5 WITH DED_AMT = IF EMP_ID NE LAST EMP_ID THEN 1 ELSE LAST CTR + 1; END TABLE FILE EMPLOYEE SUM CNT.DED_AMT NOPRINT EMP_ID LAST_NAME FIRST_NAME CURR_SAL COMPUTE DEDLEN/I5 = 12 * CNT.DED_AMT; BY EMP_ID NOPRINT SUM DED_AMT BY EMP_ID NOPRINT ACROSS CTR NOPRINT WHERE DED_CODE EQ 'FICA' ON TABLE SET HOLDLIST PRINTONLY ON TABLE HOLD AS OCCURS1 FORMAT ALPHA END
The OCCURS1 file has one record per employee with a variable number of DED_AMT fields. The total length of the number of actual instances of DED_AMT is stored in the field named DEDLEN. The Master File generated by the HOLD command lists 10 DED_AMT fields:
FILENAME=OCCURS1 , SUFFIX=FIX , $ SEGMENT=OCCURS1, SEGTYPE=S0, $ FIELDNAME=EMP_ID, ALIAS=E01, USAGE=A9, ACTUAL=A09, $ FIELDNAME=LAST_NAME, ALIAS=E02, USAGE=A15, ACTUAL=A15, $ FIELDNAME=FIRST_NAME, ALIAS=E03, USAGE=A10, ACTUAL=A10, $ FIELDNAME=CURR_SAL, ALIAS=E04, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=DEDLEN, ALIAS=E05, USAGE=I5, ACTUAL=A05, $ FIELDNAME=DED_AMT, ALIAS=E06, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=DED_AMT, ALIAS=E07, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=DED_AMT, ALIAS=E08, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=DED_AMT, ALIAS=E09, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=DED_AMT, ALIAS=E10, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=DED_AMT, ALIAS=E11, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=DED_AMT, ALIAS=E12, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=DED_AMT, ALIAS=E13, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=DED_AMT, ALIAS=E14, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=DED_AMT, ALIAS=E15, USAGE=D12.2M, ACTUAL=A12, $
You can edit the Master File to describe these repeating DED_AMT fields with an OCCURS segment. The DEFINE field named NUMOCC derives the number of occurrences from the DEDLEN field. The ORDER field is not actually in the file. It is an internal counter populated by WebFOCUS:
FILENAME=OCCURS1 , SUFFIX=FIX , $ SEGMENT=OCCURS1, SEGTYPE=S0, $ FIELDNAME=EMP_ID, ALIAS=E01, USAGE=A9, ACTUAL=A09, $ FIELDNAME=LAST_NAME, ALIAS=E02, USAGE=A15, ACTUAL=A15, $ FIELDNAME=FIRST_NAME, ALIAS=E03, USAGE=A10, ACTUAL=A10, $ FIELDNAME=CURR_SAL, ALIAS=E04, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=DEDLEN, ALIAS=E05, USAGE=I5, ACTUAL=A05, $ DEFINE NUMOCC/I2 = DEDLEN/12;,$ SEGNAME=DEDUCTION, SEGTYPE=S0, PARENT=OCCURS1, OCCURS=NUMOCC,$ FIELDNAME=DED_AMT, ALIAS=E06, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=ORDER, ALIAS=ORDER, USAGE=I2 , ACTUAL=I4 , $
The following request uses the ORDER field to select and print the first occurrence of the repeating field for each employee. Since every employee has at least one deduction, every employee is represented on the report output:
TABLE FILE OCCURS1 PRINT NUMOCC LAST_NAME CURR_SAL DED_AMT WHERE ORDER EQ 1 END
The output is:
NUMOCC LAST_NAME CURR_SAL DED_AMT ------ --------- -------- ------- 10 STEVENS $11,000.00 $64.17 8 SMITH $13,200.00 $100.10 4 JONES $18,480.00 $247.94 8 SMITH $9,500.00 $60.96 1 BANNING $29,700.00 $519.75 8 IRVING $26,862.00 $626.78 4 ROMANS $21,120.00 $317.62 1 MCCOY $18,480.00 $161.70 5 BLACKWOOD $21,780.00 $444.67 7 MCKNIGHT $16,100.00 $187.88 4 GREENSPAN $9,000.00 $52.50 10 CROSS $27,062.00 $631.40
If you print the tenth occurrence of the repeating field, only two employees are displayed on the report output:
TABLE FILE OCCURS1 PRINT NUMOCC LAST_NAME CURR_SAL DED_AMT WHERE ORDER EQ 10 END
The output is:
NUMOCC LAST_NAME CURR_SAL DED_AMT ------ --------- -------- ------- 10 STEVENS $11,000.00 $58.33 10 CROSS $27,062.00 $526.20
WebFOCUS |