title: How to use a CASE statement with a JOIN
body: <p>I am trying to use join statement that vary based on certain case statements. When the field lane.dest_postal_code_prefix is NOT NULL then I want the join to be like the first block of code below.
  When the field lane.dest_postal_code_prefix is NULL then I want the join to be like second block of code below.
  (Note the difference on the ON conditions between the two cases)</p>
  
  <p>I need some help adding the case statement to the join clause.</p>
  
  <pre><code>--WHEN lane.dest_postal_code_prefix is NOT NULL
  SELECT * FROM big_bucket_bridge A
      LEFT JOIN lane 
      ON
      (
      A.customer_country = lane.dest_country_code
      AND
      SUBSTRING( A.ddm_zip, 1, LENGTH( lane.dest_postal_code_prefix ) ) = 
      lane.dest_postal_code_prefix
          )
  
  WHERE  
      snapshot_day between '2019-06-23'-22 and '2019-06-23'
      AND (is_before_cutoff_g OR (is_before_cutoff_opt_g and not under_two))
      AND row_n =1
  ;
  
  --WHEN lane.dest_postal_code_prefix is NULL
  SELECT * FROM big_bucket_bridge A
      LEFT JOIN lane 
      ON
      (
      A.customer_country = lane.dest_country_code
      )
  
  WHERE  
      snapshot_day between '2019-06-23'-22 and '2019-06-23'
      AND (is_before_cutoff_g OR (is_before_cutoff_opt_g and not under_two))
      AND row_n =1
  ;
  </code></pre>