Conditional logic in join criteria

[janet.anderson]

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?

[rtaylor]

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

[janet.anderson]

Apparently I over-complicated it. The solution worked. Thanks!

[janet.anderson]

Apparently I over-complicated it. The solution worked. Thanks!