CASE inside a COALESCE

Question

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

 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

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:

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)

But I am receiving error. The query is quite big therefore I want to avoid temp tables. Any workaround? Thanks in advance

Answer 1

For anyone struggling with this issue, to appropriately write a CASE statement within a COALESCE statement, the code should be revised as follows:

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 time_final

Answer 2

COALESCE will return the first value that isn't NULL.

In this case this would be the following.

CASE 
  WHEN (COALESCE(T1.local_time, '') = '') AND T2.country = 'UK' THEN T2.gmt_time
  WHEN (COALESCE(T1.local_time, '') = '') AND T2.country = 'ES' THEN DATEADD(hour, 1, T2.gmt_time)
    ...
ELSE T2.gmt_time END AS final_time

See also Redshifts documentation on COALESCE/NVL Expression