The OCCURS Segment

In this section:

For use with TABLE requests, you can describe tables that contain repeating columns as OCCURS segments. Repeating data is not characteristic of normalized tables and views. However, denormalized tables may exist for some situations.

The OCCURS segment, a virtual construct, eliminates the need to specify the names of every column in a TABLE request if a group of columns contains similar data. In the OCCURS segment definition, you redefine all the separate columns as one group that shares the same name. You can then define the order field, an internal FOCUS counter that enables you to access specific columns by their sequence numbers within the group instead of by separate names.

To define an OCCURS segment, you need


Top of page

x
Creating an OCCURS Segment

To create an OCCURS segment

  1. Describe the entire table as a single-table Master File. Include a field declaration for each repeating column.
  2. Add a segment description for the related OCCURS segment. In it, include a field declaration that redefines the repeating portion of the table.

The syntax for an OCCURS segment declaration is

SEGNAME=segname, PARENT=name, POSITION=field, OCCURS=nnnn,$

where:

segname

Is the name of the OCCURS segment, up to eight characters.

parent

Is the SEGNAME value of the table that contains declarations for the repeating fields.

field

Is the name of the first repeating field in the parent table.

nnnn

Is the number of repeating fields. Acceptable values range from 1 to 4095.

Note:

The following SALARY table contains monthly payroll tax deductions for an employee, and the Master File describes the repeating columns as 12 separate deduction fields. It also includes an OCCURS segment, OCC:

 FILENAME=SALARY, SUFFIX=DB2,$
 SEGNAME=SALARY,  SEGTYPE=S0,$
  FIELD=EMPID,    ALIAS=EMPID,    USAGE=A7,   ACTUAL=A7,$
  FIELD=EMPNAME,  ALIAS=EMPNAME,  USAGE=A10,  ACTUAL=A10,$
  FIELD=SALARY,   ALIAS=PAY,      USAGE=P9.2, ACTUAL=P4,$
  FIELD=DEDUCT1,  ALIAS=DEDUCT1,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT2,  ALIAS=DEDUCT2,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT3,  ALIAS=DEDUCT3,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT4,  ALIAS=DEDUCT4,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT5,  ALIAS=DEDUCT5,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT6,  ALIAS=DEDUCT6,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT7,  ALIAS=DEDUCT7,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT8,  ALIAS=DEDUCT8,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT9,  ALIAS=DEDUCT9,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT10, ALIAS=DEDUCT10, USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT11, ALIAS=DEDUCT11, USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT12, ALIAS=DEDUCT12, USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
 SEGNAME=OCC, PARENT=SALARY, POSITION=DEDUCT1, OCCURS=12,$
  FIELD=TAX,      ALIAS=TAXDEDUC, USAGE=P9.2, ACTUAL=P8, MISSING=ON,$

The OCCURS segment redefines the 12 deduction fields in the SALARY segment, beginning with DEDUCT1. The TAX field in the OCCURS segment represents the 12 repeating fields.

The corresponding Access File does not contain a declaration for the OCCURS segment:

 SEGNAME = SALARY, TABLENAME = "USER1"."SALARY", KEYS = 1,
 WRITE = NO,   DBSPACE = PUBLIC.SPACE0,$

Top of page

x
The ORDER Field

The ORDER field is a virtual counter that assigns a sequence number to each field within a group of repeating fields. Specify this optional field when the order of data is significant. The ORDER field does not represent an existing column. It is used only for internal processing.

The ORDER field must be the last field described in the OCCURS segment. The syntax is

FIELD=name, ALIAS=ORDER, USAGE=In, ACTUAL=I4,$

where:

name

Is any meaningful name.

In

Is an integer (In) format.

Note:

In the previous SALARY table example, no column explicitly specifies the month for each TAX field. To associate the month, the next example adds the ORDER field as the last field in the OCCURS segment:

 FILENAME=SALARY, SUFFIX=DB2,$
 SEGNAME=SALARY,  SEGTYPE=S0,$
  FIELD=EMPID,    ALIAS=EMPID,    USAGE=A7,   ACTUAL=A7,$
  FIELD=EMPNAME,  ALIAS=EMPNAME,  USAGE=A10,  ACTUAL=A10,$
  FIELD=SALARY,   ALIAS=PAY,      USAGE=P9.2, ACTUAL=P4,$
  FIELD=DEDUCT1,  ALIAS=DEDUCT1,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT2,  ALIAS=DEDUCT2,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT3,  ALIAS=DEDUCT3,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT4,  ALIAS=DEDUCT4,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT5,  ALIAS=DEDUCT5,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT6,  ALIAS=DEDUCT6,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT7,  ALIAS=DEDUCT7,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT8,  ALIAS=DEDUCT8,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT9,  ALIAS=DEDUCT9,  USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT10, ALIAS=DEDUCT10, USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT11, ALIAS=DEDUCT11, USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=DEDUCT12, ALIAS=DEDUCT12, USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
 SEGNAME=OCC, PARENT=SALARY, POSITION=DEDUCT1, OCCURS=12,$
  FIELD=TAX,      ALIAS=TAXDEDUC, USAGE=P9.2, ACTUAL=P8, MISSING=ON,$
  FIELD=ORDER,    ALIAS=ORDER,    USAGE=I4,   ACTUAL=I4,$

In subsequent report requests, you can use the DECODE function to translate the ORDER field into monthly values.

In this example, a DEFINE command assigns the month to each counter value. You can specify a temporary field before the report request or in the Master File

define file salary
month/a3=decode order(1 'jan' 2 'feb' 3 'mar' 4 'apr' 5 'may'
6 'jun' 7 'jul' 8 'aug' 9 'sep' 10 'oct' 11 'nov' 12 'dec' else '   ');
end
table file salary
print last_name tot.tax if month eq 'jan'
end

You can also use the ORDER field in selection tests. For example:

table file salary
print empeid last_name tax
if order eq 12
end


Information Builders