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. |
In this section: |
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:
To create an OCCURS segment:
The syntax for an OCCURS segment declaration is
SEGNAME=segname, PARENT=name, POSITION=field, OCCURS=nnnn,$
where:
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=SQLDS,$
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,$
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:
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=SQLDS,$
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 |