Storing the Number of Repetitions of a Repeating Field in a Virtual Field

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.


Top of page

x
Syntax: How to Specify an OCCURS Segment Using a Virtual Field
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:

parent
Is the name of the parent segment.
segtype
Is the SEGTYPE of the parent segment.
definefield
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.
n
Is the format of the virtual field that describes the number of repetitions. It must be an integer format.
expression
Is a valid expression that derives the number of repetitions for each record instance.
osegname
Is the name of the descendent OCCURS segment.
rfield
Is the name of a repeating field in the OCCURS segment.
ralias
Is the alias of a repeating field in the OCCURS segment.
rufmt
Is the display format for a repeating field in the OCCURS segment.
rafmt
Is the actual format for a repeating field in the OCCURS segment.
orderfield
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.

Top of page

x
Reference: Usage Notes for Using a Virtual Field With OCCURS

The virtual field used as the OCCURS value cannot be redefined inside or outside of the Master File.



Example: Using a Virtual Field With an OCCURS Segment

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