The NORM function is used to normalize the contents of a source datastore record into one (1) or more target datastores. This function is typically used when the target datastore(s) is a relational table and the source datastore records contain de-normalized data (i.e. repeating groups/occurs).
Category
Specialized
Syntax
NORM(proc_name, repeating_group_item, 'STOPINIT' | 'NOSTOP' )
Parameters and Descriptions
| Parameter | Description |
|---|---|
| proc_name | The name of the Connect CDC SQData mapping procedure (defined with the CREATE PROC command) that performs the source to target mapping for the de-normalized data elements into the normalized data elements. |
| repeating_group_item | The name of the group item as defined in the source datastore structure as being a repeating group (occurs). The individual data elements under the group item are mapped in the Connect CDC SQData PROC proc_name. |
| 'STOPINIT' | Specifies that the processing (normalization) of the repeating group elements is to stop when either spaces or low values (binary zeros are encountered. This allows you to process only valid occurrences within the repeating group. |
| 'NOSTOP' | Specifies that all of the occurrences within the repeating group be processed, regardless of the contents of certain data elements (i.e. some of the elements may be null). |
Example
A source record DESCRIPTION is defined using the following COBOL copybook:
DESCRIPTION COBOL
/+
01 EMPLOYEE-RECORD.
05 EMP-NO PIC 9(9).
05 NAME PIC X(25).
05 STREET PIC X(25).
05 CITY PIC X(20).
05 STATE PIC X(2).
05 PAY-HIST-GRP OCCURS 12 TIMES
10 MONTH-NO PIC 9(2).
10 PAY-AMOUNT PIC 9(5)V99.
05 HOUR-HIST-GRP OCCURS 12 TIMES.
10 MONTH-NUM PIC 9(2).
10 HOURS PIC 9(3).
+/
AS EMPLOYEE;The target table DESCRIPTION for the non repeating group fields T_EMPLOYEE is defined using the SQLDDL Relational DDL:
DESCRIPTION SQLDDL
/+
CREATE TABLE EMPLOYEE_RECORD
(
,EMP_NO INTEGER(9)
,NAME VARCHAR(25)
,STREET VARCHAR(25)
,CITY VARCHAR(20)
,STATE CHAR(2)
)
+/
AS T_EMPLOYEE;The target table DESCRIPTION for the PAY-HIST-GRP is defined using the SQLDDL Relational DDL:
DESCRIPTION SQLDDL
/+
CREATE TABLE PAY_HIST (
,EMP_NO INTEGER(9)
,MONTH_NO INTEGER
,PAY_AMOUNT DECIMAL(7,2)
+/
AS T_PAY_HIST;Given the source description alias EMPLOYEE and the target description alias T_PAY_HIST above:
1. Map the non-repeating group data elements to target description T_EMPLOYEE and using NORM, call the PAY_HIST and HOUR_HIST procs to map and APPLY both normalized repeating groups.
CREATE PROC P_EMPLOYEE AS SELECT
T_EMPLOYEE.EMP_NO = EMPLOYEE.EMP-NO
T_EMPLOYEE.NAME = EMPLOYEE.NAME
T_EMPLOYEE.STREET = EMPLOYEE.STREET
T_EMPLOYEE.CITY = EMPLOYEE.CITY
T_EMPLOYEE.STATE = EMPLOYEE.STATE
APPLY (TARGET, T_EMPLOYEE)
NORM(P_PAY_HIST, PAY-HIST-GRP, 'NOSTOP')
NORM(P_HOUR_HIST, HOUR-HIST-GRP, 'STOPINIT')
FROM CDCIN;2. Normalize and map the repeating group data elements in the group PAY_HIST_GRP by calling procedure P_PAYHIST. Use the option of NOSTOP in order to get all 12 occurrences of the employee's pay history.
CREATE PROC P_PAY_HIST AS SELECT
T_PAY_HIST.EMP_NO = EMPLOYEE.EMP-NO
T_PAY_HIST.MONTH_NO = EMPLOYEE.MONTH_NO
T_PAY_HIST.PAY_AMOUNT = EMPLOYEE.PAY_AMOUNT
APPLY (TARGET, TGT_PAY_HIST)
FROM CDCIN;3. Normalize and map the repeating group data elements in the group HOUR-HIST-GRP by calling a similar procedure P_HOUR-HIST. Use the option of STOPINIT in order to process only the occurrences that contain non-zero/non-null data elements.
CREATE PROC P_PAY_HIST AS SELECT
T_HOUR_HIST.EMP_NO = EMPLOYEE.EMP-NO
T_HOUR_HIST.MONTH_NO = EMPLOYEE.MONTH_NO
T_HOUR_HIST.HOURS = EMPLOYEE.HOURS
APPLY (TARGET, T_HOUR_HIST)
FROM CDCIN;4. Call the P_EMPLOYEE proc to process the EMPLOYEE records and other Procs for other record
PROCESS INTO TARGET
SELECT
{
CASE RECNAME(CDCIN)
WHEN 'EMPLOYEE'
{ CALLPROC(P_EMPLOYEE) }
WHEN 'CONTRACT'
{ CALLPROC(P_CONTRACT) }
FROM CDCIN;