case statement to avoid interval, timestamp diff in hours

[es_skul23]

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.

[Fred]

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",

[es_skul23]

Wow, two errors...and two solutions!  Thanks so much, Fred.  This was extremely helpful.