The primary purpose of Smart Stepping is to enable n-ary merge/join operations to be accomplished as efficiently as possible. To that end the concept of a set of datasets (or indexes) has been added to the language. This allows JOIN to be extended to operate on multiple datasets, not just two.
For example, given this data (contained in the SmartStepping2.ECL file)
Rec := RECORD,MAXLENGTH(4096)
STRING1 Letter;
UNSIGNED1 DS;
UNSIGNED1 Matches := 1;
UNSIGNED1 LastMatch := 1;
SET OF UNSIGNED1 MatchDSs := [1];
END;
ds1 := DATASET([{'A',1},{'B',1},{'C',1},{'D',1},{'E',1}],Rec);
ds2 := DATASET([{'A',2},{'B',2},{'H',2},{'I',2},{'J',2}],Rec);
ds3 := DATASET([{'B',3},{'C',3},{'M',3},{'N',3},{'O',3}],Rec);
ds4 := DATASET([{'A',4},{'B',4},{'R',4},{'S',4},{'T',4}],Rec);
ds5 := DATASET([{'B',5},{'V',5},{'W',5},{'X',5},{'Y',5}],Rec); To do an inner join on all five datasets using Smart Stepping the code is this (also contained in the SmartStepping2.ECL file):
SetDS := [ds1,ds2,ds3,ds4,ds5]; Rec XF(Rec L,DATASET(Rec) Matches) := TRANSFORM SELF.Matches := COUNT(Matches); SELF.LastMatch := MAX(Matches,DS); SELF.MatchDSs := SET(Matches,DS); SELF := L; END; j1 := JOIN( SetDS,STEPPED(LEFT.Letter=RIGHT.Letter),XF(LEFT,ROWS(LEFT)),SORTED(Letter)); O1 := OUTPUT(j1);
Without using Smart Stepping the code is this (also contained in the SmartStepping2.ECL file):
Rec XF1(Rec L,Rec R,integer MatchSet) := TRANSFORM SELF.Matches := L.Matches + 1; SELF.LastMatch := MatchSet; SELF.MatchDSs := L.MatchDSs + [MatchSet]; SELF := L; END; j2 := JOIN( ds1,ds2,LEFT.Letter=RIGHT.Letter,XF1(LEFT,RIGHT,2)); j3 := JOIN( j2,ds3, LEFT.Letter=RIGHT.Letter,XF1(LEFT,RIGHT,3)); j4 := JOIN( j3,ds4, LEFT.Letter=RIGHT.Letter,XF1(LEFT,RIGHT,4)); j5 := JOIN( j4,ds5, LEFT.Letter=RIGHT.Letter,XF1(LEFT,RIGHT,5)); O2 := OUTPUT(SORT(j5,Letter));
Both of these examples produce the same one-record output, but without Smart Stepping you need four separate JOINs to accomplish the goal, and in "real world" code you might need a separate TRANSFORM for each, depending on what result you were trying to produce.
In addition to the standard inner join between all the datasets, the Smart Stepping form of JOIN also supports the same type of LEFT OUTER and LEFT ONLY joins as the standard JOIN operation. However, this form also supports M of N joins (MOFN), where matching records must appear in a specified minimum number of the datasets, and may optionally specify a maximum in which they appear, as in these examples (also contained in the SmartStepping2.ECL file):
j6 := JOIN( SetDS,
STEPPED(LEFT.Letter=RIGHT.Letter),
XF(LEFT,ROWS(LEFT)),
SORTED(Letter),
LEFT OUTER);
j7 := JOIN( SetDS,
STEPPED(LEFT.Letter=RIGHT.Letter),
XF(LEFT,ROWS(LEFT)),
SORTED(Letter),
LEFT ONLY);
j8 := JOIN( SetDS,
STEPPED(LEFT.Letter=RIGHT.Letter),
XF(LEFT,ROWS(LEFT)),
SORTED(Letter),
MOFN(3));
j9 := JOIN( SetDS,
STEPPED(LEFT.Letter=RIGHT.Letter),
XF(LEFT,ROWS(LEFT)),
SORTED(Letter),
MOFN(3,4));
O3 := OUTPUT(j6);
O4 := OUTPUT(j7);
O5 := OUTPUT(j8);
O6 := OUTPUT(j9);
The RANGE function is also available to limit which datasets in the set of datasets will be processed, as in this example (also contained in the SmartStepping2.ECL file):
j10 := JOIN( RANGE(SetDS,[1,3,5]),
STEPPED(LEFT.Letter=RIGHT.Letter),
XF(LEFT,ROWS(LEFT)),
SORTED(Letter));
O7 := OUTPUT(j10);
SEQUENTIAL(O1,O2,O3,O4,O5,O6,O7);This feature can be useful in situations where you may not have all the information to select from all the datasets in the set.
This next example demonstrates the most probable use for this technology in the real world--finding the set of parent records where related child records exist that fit a specified set of filter criteria. That's exactly what this example (contained in the SmartStepping3.ECL file) does:
LinkRec := RECORD UNSIGNED1 Link; END; DS_Rec := RECORD(LinkRec) STRING10 Name; STRING10 Address; END; Child1_Rec := RECORD(LinkRec) UNSIGNED1 Nbr; END; Child2_Rec := RECORD(LinkRec) STRING10 Car; END; Child3_Rec := RECORD(LinkRec) UNSIGNED4 Salary; END; Child4_Rec := RECORD(LinkRec) STRING10 Domicile; END;
Using this form of RECORD structure inheritance makes it very simple to define the linkage between the parent and child files. Note also that all these files have different formats.
ds := DATASET([{1,'Fred','123 Main'},{2,'George','456 High'},
{3,'Charlie','789 Bank'},{4,'Danielle','246 Front'},
{5,'Emily','613 Boca'},{6,'Oscar','942 Frank'},
{7,'Felix','777 John'},{8,'Adele','543 Bank'},
{9,'Johan','123 Front'},{10,'Ludwig','212 Front'}],
DS_Rec);
Child1 := DATASET([{1,5},{2,8},{3,11},{4,14},{5,17},
{6,20},{7,23},{8,26},{9,29},{10,32}],Child1_Rec);
Child2 := DATASET([{1,'Ford'},{2,'Ford'},{3,'Chevy'},
{4,'Lexus'},{5,'Lexus'},{6,'Kia'},
{7,'Mercury'},{8,'Jeep'},{9,'Lexus'},
{9,'Ferrari'},{10,'Ford'}],
Child2_Rec);
Child3 := DATASET([{1,10000},{2,20000},{3,155000},{4,800000},
{5,250000},{6,75000},{7,200000},{8,15000},
{9,80000},{10,25000}],
Child3_Rec);
Child4 := DATASET([{1,'House'},{2,'House'},{3,'House'},{4,'Apt'},
{5,'Apt'},{6,'Apt'},{7,'Apt'},{8,'House'},
{9,'Apt'},{10,'House'}],
Child4_Rec);
TblRec := RECORD(LinkRec),MAXLENGTH(4096)
UNSIGNED1 DS;
UNSIGNED1 Matches := 0;
UNSIGNED1 LastMatch := 0;
SET OF UNSIGNED1 MatchDSs := [];
END;
Filter1 := Child1.Nbr % 2 = 0;
Filter2 := Child2.Car IN ['Ford','Chevy','Jeep'];
Filter3 := Child3.Salary < 100000;
Filter4 := Child4.Domicile = 'House';
t1 := PROJECT(Child1(Filter1),TRANSFORM(TblRec,SELF.DS:=1,SELF:=LEFT));
t2 := PROJECT(Child2(Filter2),TRANSFORM(TblRec,SELF.DS:=2,SELF:=LEFT));
t3 := PROJECT(Child3(Filter3),TRANSFORM(TblRec,SELF.DS:=3,SELF:=LEFT));
t4 := PROJECT(Child4(Filter4),TRANSFORM(TblRec,SELF.DS:=4,SELF:=LEFT));The PROJECT operation is a simple way to transform the results for all these different format files into a single standard layout that can be used by the Smart Stepping JOIN operation.
SetDS := [t1,t2,t3,t4];
TblRec XF(TblRec L,DATASET(TblRec) Matches) := TRANSFORM
SELF.Matches := COUNT(Matches);
SELF.LastMatch := MAX(Matches,DS);
SELF.MatchDSs := SET(Matches,DS);
SELF := L;
END;
j1 := JOIN( SetDS,STEPPED(LEFT.Link=RIGHT.Link),XF(LEFT,ROWS(LEFT)),SORTED(Link));
OUTPUT(j1);
OUTPUT(ds(link IN SET(j1,link)));The first OUTPUT simply displays the same kind of result as the previous example. The second OUTPUT produces the "real-world" result set of the base dataset records that match the filter criteria for each of the child datasets.