Skip to main content

DENORMALIZE

DENORMALIZE(parentrecset, childrecset, condition, transform [,LOCAL] [,NOSORT] [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )

DENORMALIZE(parentrecset, childrecset, condition, GROUP, transform [,LOCAL] [,NOSORT] [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )

parentrecsetThe set of parent records to process, already in the format that will contain the denormalized parent and child records.
childrecsetThe set of child records to process.
conditionAn expression that specifies how to match records between the parentrecset and childrecset.
transformThe TRANSFORM function to call.
LOCALOptional. Specifies the operation is performed on each supercomputer node independently, without requiring interaction with all other nodes to acquire data; the operation maintains the distribution of any previous DISTRIBUTE.
NOSORTOptional. Specifies the operation is performed without sorting the parentrecset or childrecset --both must already be sorted so matching records in both are in order. This allows programmer control of the order of the child records.
GROUPSpecifies grouping the childrecset records based on the join condition so all the related child records are passed as a dataset parameter to the transform.
UNORDEREDOptional. Specifies the output record order is not significant.
ORDEREDSpecifies the significance of the output record order.
boolWhen False, specifies the output record order is not significant. When True, specifies the default output record order.
STABLEOptional. Specifies the input record order is significant.
UNSTABLEOptional. Specifies the input record order is not significant.
PARALLELOptional. Try to evaluate this activity in parallel.
numthreadsOptional. Try to evaluate this activity using numthreads threads.
ALGORITHMOptional. Override the algorithm used for this activity.
nameThe algorithm to use for this activity. Must be from the list of supported algorithms for the SORT function's STABLE and UNSTABLE options.
Return:DENORMALIZE returns a record set.

The DENORMALIZE function is used to form a combined record out of a parent and any number of children. It acts very similar to a JOIN except that where JOIN with one parent and three children would call the transform three times and produce three outputs, DENORMALIZE calls the transform three times where the input to the first transform is the parent and one child, the input to the second transform is the output of the first transform and another child, and the input to the third transform is the output from the second transform and the remaining child. Also like JOIN, the order in which the childrecset records are sent to the transform is undefined.

Because DENORMALIZE is basically a specialized form of JOIN, the various join types (LEFT OUTER, RIGHT OUTER, FULL OUTER, LEFT ONLY, RIGHT ONLY, FULL ONLY) are also available for use on DENORMALIZE and act just as they do with JOIN.

All JOIN options are available for DENORMALIZE. See Join Options for details.

DENORMALIZE TRANSFORM Function Requirements

For form one, the transform function must take at least two parameters: a LEFT record of the same format as the combined parentrecset and childrecset (the resulting de-normalized record structure), and a RIGHT record of the same format as the childrecset. An optional third parameter may be specified: an integer COUNTER specifying the number of times the transform has been called for the current set of parent/child pairs (defined by the condition values). The result of the transform function must be a record set of the same format as the LEFT record.

For form two, the transform function must take at least two parameters: a LEFT record of the same format as the combined parentrecset and childrecset (the resulting de-normalized record structure), and ROWS(RIGHT) dataset of the same format as the childrecset. The result of the transform function must be a record set of the same format as the LEFT record.

Example:

Form 1 example:

NormRec := RECORD
  STRING20  thename;
  STRING20  addr;
END;
NamesRec := RECORD
  UNSIGNED1  numRows;
  STRING20  thename; 
  STRING20  addr1 := '';
  STRING20  addr2 := '';
  STRING20  addr3 := '';
  STRING20  addr4 := '';
END;
NamesTable := DATASET([ {0,'Kevin'},{0,'Liz'},{0,'Mr Nobody'},
                        {0,'Anywhere'}], NamesRec);
NormAddrs := DATASET([{'Kevin','10 Malt Lane'},
                      {'Liz','10 Malt Lane'},
                      {'Liz','3 The cottages'},
                      {'Anywhere','Here'},
                      {'Anywhere','There'},
                      {'Anywhere','Near'},
                      {'Anywhere','Far'}],NormRec);
NamesRec DeNormThem(NamesRec L, NormRec R, INTEGER C) := TRANSFORM
    SELF.NumRows := C;
    SELF.addr1 := IF (C=1, R.addr, L.addr1);
    SELF.addr2 := IF (C=2, R.addr, L.addr2);
    SELF.addr3 := IF (C=3, R.addr, L.addr3);
    SELF.addr4 := IF (C=4, R.addr, L.addr4);
    SELF := L;
END;
DeNormedRecs := DENORMALIZE(NamesTable, NormAddrs,
                            LEFT.thename = RIGHT.thename,
                            DeNormThem(LEFT,RIGHT,COUNTER));
OUTPUT(DeNormedRecs);

Form 2 example:

NormRec := RECORD
  STRING20  thename;
  STRING20  addr;
END;
NamesRec := RECORD
  UNSIGNED1  numRows;
  STRING20  thename;
  DATASET(NormRec) addresses;
END;
NamesTable := DATASET([ {0,'Kevin',[]},{0,'Liz',[]},
                        {0,'Mr Nobody',[]},{0,'Anywhere',[]}],
                      NamesRec);
NormAddrs := DATASET([{'Kevin','10 Malt Lane'},
                      {'Liz','10 Malt Lane'},
                      {'Liz','3 The cottages'},
                      {'Anywhere','Here'},
                      {'Anywhere','There'},
                      {'Anywhere','Near'},
                      {'Anywhere','Far'}],NormRec);
NamesRec DeNormThem(NamesRec L, DATASET(NormRec) R) := TRANSFORM
    SELF.NumRows := COUNT(R);
    SELF.addresses := R;
    SELF := L;
END;
DeNormedRecs := DENORMALIZE(NamesTable, NormAddrs,
                           LEFT.thename = RIGHT.thename,
                           GROUP,
                           DeNormThem(LEFT,ROWS(RIGHT)));
OUTPUT(DeNormedRecs);

NOSORT example:

MyRec := RECORD
  STRING1 Value1;
  STRING1 Value2;
END;
ParentFile := DATASET([{'A','C'},{'B','B'},{'C','A'}],MyRec);
ChildFile  := DATASET([{'A','Z'},{'A','T'},{'B','S'},{'B','Y'},
                       {'C','X'},{'C','W'}],MyRec);
MyOutRec := RECORD
  ParentFile.Value1;
  ParentFile.Value2;
  STRING1 CVal2_1 := '';
  STRING1 CVal2_2 := '';
END;
P_Recs := TABLE(ParentFile, MyOutRec);
MyOutRec DeNormThem(MyOutRec L, MyRec R, INTEGER C) := TRANSFORM
 SELF.CVal2_1 := IF(C = 1, R.Value2, L.CVal2_1);
 SELF.CVal2_2 := IF(C = 2, R.Value2, L.CVal2_2);
 SELF := L;
END;
DeNormedRecs := DENORMALIZE(P_Recs, ChildFile,
                            LEFT.Value1 = RIGHT.Value1,
                            DeNormThem(LEFT,RIGHT,COUNTER),NOSORT);
OUTPUT(DeNormedRecs);
/* DeNormedRecs result set is:
 Rec#  Value1 PVal2  CVal2_1  CVal2_2
 1       A      C      Z         T
 2       B      B      S         Y
 3       C      A      X         W
 */

See Also: JOIN, TRANSFORM Structure, RECORD Structure, NORMALIZE