Fri Sep 17, 2021 11:14 am
Login Register Lost Password? Contact Us


Conditional logic in join criteria

Comments and questions related to the Enterprise Control Language

Wed Jul 21, 2021 9:33 pm Change Time Zone

My team has a table of criteria that we want excluded from a dataset. It has a layout like {STRING CarrierCode, SET OF STRING StateList, STRING8 BeginDate, STRING8 EndDate}. So I want to join my exclude table to the dataset with criterial like
Code: Select all
LEFT.carrier = RIGHT.CarrierCode          
AND LEFT.state in RIGHT.StateList
AND LEFT.date BETWEEN RIGHT.BeginDate AND RIGHT.EndDate


The catch is that the StateList can be something like ['All'] that implies there should be no join criteria for state. So what I would really like is more like
Code: Select all
LEFT.carrier = RIGHT.CarrierCode
#IF RIGHT.statelist[1] <> 'All'      
AND LEFT.state in RIGHT.StateList
#END
AND LEFT.date BETWEEN RIGHT.BeginDate AND RIGHT.EndDate


Except that code doesn't work. Can you suggest a way to make conditional join criteria work?
janet.anderson
 
Posts: 44
Joined: Wed Jul 17, 2013 5:31 pm

Thu Jul 22, 2021 12:29 pm Change Time Zone

Janet,

Try it like this:
Code: Select all
JOIN(lds,rds,
     IF(RIGHT.statelist[1] = 'All',     
        LEFT.carrier = RIGHT.CarrierCode
          AND LEFT.date BETWEEN RIGHT.BeginDate AND RIGHT.EndDate,
        LEFT.carrier = RIGHT.CarrierCode
          AND LEFT.state in RIGHT.StateList
          AND LEFT.date BETWEEN RIGHT.BeginDate AND RIGHT.EndDate));

Standard IF..THEN..ELSE with BOOLEAN expressions as the true/false returns.

HTH,

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

Thu Jul 22, 2021 1:51 pm Change Time Zone

Apparently I over-complicated it. The solution worked. Thanks!
janet.anderson
 
Posts: 44
Joined: Wed Jul 17, 2013 5:31 pm

Thu Jul 22, 2021 1:51 pm Change Time Zone

Apparently I over-complicated it. The solution worked. Thanks!
janet.anderson
 
Posts: 44
Joined: Wed Jul 17, 2013 5:31 pm


Return to ECL

Who is online

Users browsing this forum: No registered users and 1 guest