Redshift - Error using Case statement

Question

I am trying to check time since first call was made to a customer. I am checking this against current system time using a case statement.

select sale_id,case when ((CURRENT_TIMESTAMP - min(call_date) >= '500 days' THEN 'more than 500 days'
                    when ((CURRENT_TIMESTAMP - min(call_date) >= '300 days' THEN 'more than 300 days'
                    else 'Less than 300 days' end  as Aging
                    from sales

I keep getting Query execution failed. Invalid operation: syntax error at or neat "THEN".

I am using Amazon Redshift DB. Could anyone assist.

You also trying to compare time with string which is not valid — apomene
@apomene: the comparison is correct. The result of CURRENT_TIMESTAMP - min(call_date) is an interval and '500 days' is valid literal for an interval — a_horse_with_no_name

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-07-04T11:35:54

In addition to the parentheses issue, you are also using min() with no group by. I also doubt that sale_id represents a customer.

So, I suspect you want something like:

select customer_id,  -- or whatever the right column is
       (case when CURRENT_TIMESTAMP - min(call_date) >= '500 days' then 'more than 500 days'
             when CURRENT_TIMESTAMP - min(call_date) >= '300 days' then 'more than 300 days'
             else 'Less than 300 days'
        end)  as Aging
from sales 
group by customer_id;

Redshift - Error using Case statement

1 Answers