Sun Aug 19, 2018 3:58 pm
Login Register Lost Password? Contact Us


Ignore transform on LEFT OUTER join non match.

Comments and questions related to the Enterprise Control Language

Fri Dec 15, 2017 8:08 pm Change Time Zone

So I was wondering if there's a way to have a LEFT OUTER join not go through the transform function on non-matches.

Currently the following code will blank out records 2 and 3:

Code: Select all
rec := {string a, string b, string c};
DS1 := DATASET([{'1','',''}, {'2','Brian','Ge'}, {'3','Kate','Wins'}, {'4','Greg',''}], rec);
DS2 := DATASET([{'1','Fred','Flintstone'}, {'4','Jerry','Fring'}], rec);

result := JOIN(DS1, DS2,
   LEFT.a = RIGHT.a, TRANSFORM(rec,
   SELF := RIGHT
   ), LEFT OUTER);
OUTPUT(result);

This makes sense because every record goes through the transform and those with no matches are getting set to an empty record.

I can accomplish what I need like so:
Code: Select all
rec := {string a, string b, string c};
DS1 := DATASET([{'1','',''}, {'2','Brian','Ge'}, {'3','Kate','Wins'}, {'4','Greg',''}], rec);
DS2 := DATASET([{'1','Fred','Flintstone'}, {'4','Jerry','Fring'}], rec);

result := JOIN(DS1, DS2,
   LEFT.a = RIGHT.a, TRANSFORM(rec,
   SELF.b := IF(RIGHT.a <> '', RIGHT.b, LEFT.b),
   SELF.c := IF(RIGHT.a <> '', RIGHT.c, LEFT.c),
   SELF := LEFT
   ), LEFT OUTER);
OUTPUT(result);


However this method would require including every single field we want from the RIGHT record. I feel like there might be a better way to do this.

EDIT - I figured out a way to do it nicely:
Code: Select all
rec := {string a, string b, string c};
DS1 := DATASET([{'1','',''}, {'2','Brian','Ge'}, {'3','Kate','Wins'}, {'4','Greg',''}], rec);
DS2 := DATASET([{'1','Fred','Flintstone'}, {'4','Jerry','Fring'}], rec);

result := JOIN(DS1, DS2,
   LEFT.a = RIGHT.a, TRANSFORM(rec,
   SELF := IF(RIGHT.a <> '', RIGHT, LEFT)
   ), LEFT OUTER);
OUTPUT(result);
BGehalo
 
Posts: 12
Joined: Thu Sep 28, 2017 8:06 pm

Mon Dec 18, 2017 4:03 pm Change Time Zone

BGehalo,

I'm assuming that your real task is taking a dataset of update records (DS2) and "overwriting" the base file (DS1) records with the relevant changes.

I'm also assuming that your base file may contain a "bazillion" records and the number of (daily? weekly?) updates is a relatively small percentage of the base file records.

So, given those two assumptions, here's an even simpler way to accomplish it without using JOIN at all:
Code: Select all
rec := {string a, string b, string c};
DS1 := DATASET([{'1','',''}, {'2','Brian','Ge'},
                {'3','Kate','Wins'}, {'4','Greg',''}], rec);
DS2 := DATASET([{'1','Fred','Flintstone'},
                {'4','Jerry','Fring'},
                {'5','Joe','Schmo'}], rec);

SetUpdRecs := SET(DS2,a);
DS3 := DS1(a NOT IN SetUpdRecs) + DS2;
SORT(DS3,a);
This version also handles Added records along with the Updated records in your DS2 file. Strictly speaking, the SORT isn't necessary, I just added it to make the first four records result exactly the same as yours.

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1370
Joined: Wed Oct 26, 2011 7:40 pm

Mon Dec 18, 2017 4:08 pm Change Time Zone

rtaylor wrote:BGehalo,

I'm assuming that your real task is taking a dataset of update records (DS2) and "overwriting" the base file (DS1) records with the relevant changes.

I'm also assuming that your base file may contain a "bazillion" records and the number of (daily? weekly?) updates is a relatively small percentage of the base file records.

So, given those two assumptions, here's an even simpler way to accomplish it without using JOIN at all:
Code: Select all
rec := {string a, string b, string c};
DS1 := DATASET([{'1','',''}, {'2','Brian','Ge'},
                {'3','Kate','Wins'}, {'4','Greg',''}], rec);
DS2 := DATASET([{'1','Fred','Flintstone'},
                {'4','Jerry','Fring'},
                {'5','Joe','Schmo'}], rec);

SetUpdRecs := SET(DS2,a);
DS3 := DS1(a NOT IN SetUpdRecs) + DS2;
SORT(DS3,a);
This version also handles Added records along with the Updated records in your DS2 file. Strictly speaking, the SORT isn't necessary, I just added it to make the first four records result exactly the same as yours.

HTH,

Richard


Oh cool thanks, didn't think of doing it that way!
BGehalo
 
Posts: 12
Joined: Thu Sep 28, 2017 8:06 pm


Return to ECL

Who is online

Users browsing this forum: No registered users and 1 guest

cron