Hi, I am trying to avoid the interval overflow issue with the following case statement but I'm getting an error.
Is something like this possible?
case when cast(first_apprv_or_denltmx as date) - cast(Rcpt_Time_adj as date) > 416 then 9999 else
First_Apprv_or_Denltmx - Rcpt_Time_adj hour(4) end as "A hrs rcpt-dec",
Error: Syntax error: expected something like and END keyword between the word 'Rcpt_Time_adj' and the 'HOUR' keyword.
Thank you.
As far as the syntax is concerned, parentheses in the ELSE clause will resolve the initial error, but then you'lll still have an error because the THEN and ELSE have different data types.
This will pass syntax check
case when cast(first_apprv_or_denltmx as date) - cast(Rcpt_Time_adj as date) > 416 then INTERVAL '9999' HOUR else
( First_Apprv_or_Denltmx - Rcpt_Time_adj hour(4) ) end as "A hrs rcpt-dec",
and so will this
case when cast(first_apprv_or_denltmx as date) - cast(Rcpt_Time_adj as date) > 416 then 9999 else
CAST(( First_Apprv_or_Denltmx - Rcpt_Time_adj hour(4) ) AS INTEGER ) end as "A hrs rcpt-dec",
Wow, two errors...and two solutions! Thanks so much, Fred. This was extremely helpful.