A group of data items in a database that have multiple occurrences within a single record in the database are referred to as record arrays.
01 EMPLOYEE-RECORD. 05 EMP-LAST-NAME PIC X(20). 05 EMP-FIRST-NAME PIC X(20). 05 EMPSSN PIC 9(9). ...... 05 DEPENDENTS-ARRAY OCCURS 20 TIMES 10 DEP-SSN PIC 9(9). 10 DEP-NAME PIC X(20). 10 DEP-DOB PIC 9(6). 10 DEP-RELATIONSHIP-TO-EMPL PIC X.
01 EMPLOYEE-RECORD. 05 EMP-LAST-NAME PIC X(20). 05 EMP-FIRST-NAME PIC X(20). 05 EMP-SSN PIC 9(9). ....... 05 NUMBER-OF-DEPENDENTS PIC 9(4) COMP. 05 DEPENDENTS-ARRAY OCCURS 1 TO 20 TIMES DEPENDING ON NUMBER-OF-DEPENDENTS. 10 DEP-SSN PIC 9(9). 10 DEP-NAME PIC X(20). 10 DEP-DOB PIC 9(6). 10 DEP-GENDER PIC X. .......
CREATE TABLE CAC.EMPL .....
( EMP_SSN SOURCE DEFINITION DATAMAP OFFSET 40 LENGTH 9 DATATYPE C USE AS CHAR(9), NUMBER_OF_DEPENDENTS SOURCE DEFINITION DATAMAP OFFSET 49 LENGTH 2 DATATYPE H USE AS SMALLINT, BEGINLEVEL 1 OFFSET 51 LENGTH 36 OCCURS 20 DEPENDING ON COLUMN NUMBER_OF_DEPENDENTS, DEP_SSN SOURCE DEFINITION DATAMAP OFFSET 0 LENGTH 9 DATATYPE C USE AS CHAR(9), DEP_NAME SOURCE DEFINITION DATAMAP OFFSET 9 LENGTH 20 DATATYPE C USE AS CHAR(20), ENDLEVEL 1 )
The example only maps the employee’s social security number field, the COBOL DEPENDING ON variable, the dependent social security number field, and the dependent name field. The items in the OCCURS clause DEPENDENTS-ARRAY are contained within a BEGINLEVEL, ENDLEVEL block of statements. The block of statements identifies a group of data items that repeat. When converting this mapping from a COBOL record to SQL columns, Classic Federation combines each occurrence of the DEPENDENT-ARRAY with the non-array data items to create SQL rows. In this case, each occurrence of the array data items DEP-SSN and DEP-NAME is combined with the non-array data items EMP-SSN and NUMBER-OF-DEPENDENTS.
For example, if the record in the database for the employee social security number '123456789' contains three dependents, three distinct rows are returned for that particular record.
The following query returns the following result set:
Query:
SELECT EMP_SSN, NUMBER_OF_DEPENDENTS, DEP_SSN, DEP_NAME FROM CAC.EMPL WHERE EMP_SSN = '123456789';
Result set:
EMP_SSN NUMBER_OF_DEPENDENTS DEP_SSN DEP_NAME 123456789 3 111223333 Depen1 123456789 3 222334444 Depen2 123456789 3 333445555 Depen3
Restrictions
Classic Federation does not support nested OCCURS DEPENDING ON definitions. You cannot define a table that contains fixed columns after an OCCURS DEPENDING ON construct.
Record arrays that contain a fixed number of occurrences can include a NULL IS definition. These array occurrences are skipped as SQL ROW candidates at runtime. The NULL IS definition identifies a comparison value for the array itself or a column in the array that identifies an instance of the array as NULL. NULL instances of a record array are not returned as a row in the result set unless ALL instances of the array are NULL. If all instances of the array are NULL, then Classic Federation returns a single row for the non-array information in the record and sets the array data items to NULL.