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 ) ] )
| parentrecset | The set of parent records to process, already in the format that will contain the denormalized parent and child records. |
| childrecset | The set of child records to process. |
| condition | An expression that specifies how to match records between the parentrecset and childrecset. |
| transform | The TRANSFORM function to call. |
| LOCAL | Optional. 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. |
| NOSORT | Optional. 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. |
| GROUP | Specifies grouping the childrecset records based on the join condition so all the related child records are passed as a dataset parameter to the transform. |
| UNORDERED | Optional. Specifies the output record order is not significant. |
| ORDERED | Specifies the significance of the output record order. |
| bool | When False, specifies the output record order is not significant. When True, specifies the default output record order. |
| STABLE | Optional. Specifies the input record order is significant. |
| UNSTABLE | Optional. Specifies the input record order is not significant. |
| PARALLEL | Optional. Try to evaluate this activity in parallel. |
| numthreads | Optional. Try to evaluate this activity using numthreads threads. |
| ALGORITHM | Optional. Override the algorithm used for this activity. |
| name | The 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.
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