case statement to avoid interval, timestamp diff in hours

Database

case statement to avoid interval, timestamp diff in hours

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.

2 REPLIES
Teradata Employee

Re: case statement to avoid interval, timestamp diff in hours

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

Re: case statement to avoid interval, timestamp diff in hours

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