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

Solution

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

Comments

[melpomene]

Shouldn't that be ... OR T1.local_time IS NULL?

[cadv]

Thanks! That actually solved my first code therefore I do not need the COALESCE.

[Gordon Linoff]

Sample data and desired results would really help.