All Forums Database
ysrinu 44 posts Joined 04/07
13 Jun 2012
Update Interval column on Interval substraction

Hi, i have these 2 columns defined:

WAIT_INTVL INTERVAL HOUR(3) TO SECOND(2)
HOLD_INTVL INTERVAL HOUR(3) TO SECOND(2)

and both are null values

a) select COALESCE(WAIT_INTVL, INTERVAL '0' SECOND) from TableT1
returns     0:00:00.00

b) i can substract (a) with itself

select COALESCE(WAIT_INTVL, INTERVAL '0' SECOND) - COALESCE(WAIT_INTVL, INTERVAL '0' SECOND)
from TableT1
returns     0:00:00.00

c) Now, when i try to insert the above result into the INTERVAL column, i get error:

update TableT1
set HALT_INTVL =  COALESCE(WAIT_INTVL, INTERVAL '0' SECOND) - COALESCE(WAIT_INTVL, INTERVAL '0' SECOND)

It throws the error:

5405: Interval field overflow

why?

Note: My actual code does difference on 2 different columns and at runtime if they are both null (and the code colesces them to Zero interval) then the
sql fails at this update statement

Thanks,
-srinivas yelamanchili
 

ulrich 816 posts Joined 09/09
13 Jun 2012

check the type of INTERVAL '0' SECOND - it is not an interval hour(3) to second(2).

Try cast('0:00:00' as interval Hour(3) to Second(2)) instead.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ysrinu 44 posts Joined 04/07
14 Jun 2012

It worked after i added CAST to addition or substration of interval columns.

Even if the columns are not nullable and i don't use COALESCE, the sum of two intervals fail to load into an interval column, even if all the intervals are of same precision and even if the sum is not an overflow value.

I just had to cast the result of evaluation before assigning to an update column

Thanks,

-sri

You must sign in to leave a comment.