title: CASE inside a COALESCE
body: <p>I have two Redshift tables with timestamp values. One of them have local with some nulls and the other have GMT but no nulls. I also have a country column in table 2. 
  I want to query the local time values, and use COALESCE with CASE depending on the country for the null values.
  I have tried with</p>
  
  <pre><code> CASE 
          WHEN (T1.local_time = '' OR T1.local_time=NULL) AND T2.country = 'UK'  THEN T2.gmt_time
          WHEN (T1.local_time = '' OR T1.local_time=NULL) AND T2.country = 'ES' DATEADD(hour, 1, T2.gmt_time)
          ...
   ELSE T2.gmt_time END AS final_time
  </code></pre>
  
  <p>but it was not capturing null values. COALESCE (NVL) allows me to capture NULL values but I am not sure where to place the case. I have tried:</p>
  
  <pre><code>COALESCE (T1.local_time, 
            CASE
              WHEN T2.country = 'UK' THEN DA
              WHEN T2.COUNTRY = 'SP' THEN DATEADD(hour, 1, T2.gmt_time)
              ...
            ELSE T2.gmt_time END AS local_time) 
  AS time_final)
  </code></pre>
  
  <p>But I am receiving error. The query is quite big therefore I want to avoid temp tables. Any workaround?
  Thanks in advance</p>