CODEWITHSUNDEEP
sqlamazon-redshiftcoalesce
cadv
cadv

Reputation: 69

CASE inside a COALESCE

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

Upvotes: 3

Views: 36905

Answers (4)

Negative Correlation
Negative Correlation

Reputation: 983

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

Upvotes: 3

seasick
seasick

Reputation: 446

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

Upvotes: 1

Samuel Negri
Samuel Negri

Reputation: 519

I don't think you need to use coalesce here, you can just add the case when local_time is not null:

          CASE
            WHEN T1.local_time IS NOT NULL AND T1.local_time != '' THEN T1.local_time
            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)

Upvotes: -1

Mureinik
Mureinik

Reputation: 311143

Null is not a value, it's the lack thereof. You can't evaluate it with the = operator (that checks that two values are equal), but have to use the is operator:

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

Upvotes: 0

Related Questions