Joining Unrelated Datasets

[mo0926]

Hello, I was wondering if in ECL we have an easy way of combining two datasets without any relationship between the datasets?

For example:

layoyt1 :=record
string phone
string first_seen
string last_seen
End
ds1:=table(infile1, layout1, phone, first_seen, last_seen);

layout2 := record
string buildversion;
end;
ds2:= table(infile2, layout2, buildversion);

What would be the easiest way to add buildversion if the date falls between first_seen and last_seen?

Output := record
string phone
string buildversion
string first_seen
string last_seen
End

Thanks for your help in advance.

[rtaylor]

mo0926,

Hello, I was wondering if in ECL we have an easy way of combining two datasets without any relationship between the datasets?

The ONLY way to JOIN two datasets that actually have no relationship (in any language) would be to have a JOIN condition of TRUE. That would result in every record on the left matching every record on the right (i.e. if you have 10 recs on the left and 10 on the right you would end up with a 100 record result). Otherwise, you MUST have some data in both that can be used to match the records.

What would be the easiest way to add buildversion if the date falls between first_seen and last_seen?

Given this question, I have to assume that you have a date value that can be parsed from that buildversion string. Given that assumption, you could do it something like this:

Code: Select all

//I'm assuming:
// all string dates are in YYYYMMDD format
// a date is in characters 10 through 17 of the buildversion -- YMMV :)
IMPORT Std;
BOOLEAN IsInRange(Std.Date.Date_t d,
                  Std.Date.Date_t f,
                  Std.Date.Date_t l) :=
                    Std.Date.FromJulianDate(d) BETWEEN
                    Std.Date.FromJulianDate(f) AND
                    Std.Date.FromJulianDate(l);
JOIN(ds1,ds2,
     IsInRange((Std.Date.Date_t)RIGHT.buildversion[10..17],
               (Std.Date.Date_t)LEFT.first_seen,
               (Std.Date.Date_t)LEFT.last_seen),
     TRANSFORM(Output,SELF := LEFT,SELF := RIGHT));

HTH,

Richard