Failure 7453 Interval field overflow

Analytics
Enthusiast

Failure 7453 Interval field overflow

I have a teradata procedure
-- Cheking that FULFILLMENT_LINE has finished updating

sel ((Current_Timestamp - Process_Timestamp) hour(2) to second(6)) TIMEPROC
from EDWPROD.ETL_Control_Table
WHERE Process_Timestamp in

(sel max(Process_Timestamp)
from EDWPROD.ETL_Control_Table
WHERE Target_Tablename = 'FULFILLMENT_LINE'
)
and Target_Tablename = 'FULFILLMENT_LINE'
AND extract(MINUTE from TIMEPROC) <= -60
AND extract(HOUR from TIMEPROC) = 0
;

.if errorcode <> 0 then .exit 8
.if activitycount <> 0 then .exit 8

that returns with Failure 7453

I am not sure what happened. Everything was fine until about two hours back. I am not sure how to proceed and fix this. I looked at the max and min for the process_timestamp and do not show anything abnormal. Any help would be appreciated.

When I just do the first part of the sql,
sel ((Current_Timestamp - Process_Timestamp) hour(2) to second(6)) TIMEPROC
from EDWPROD.ETL_Control_Table

I get the same error,

When I do,

sel ((Current_Timestamp - Process_Timestamp) hour(2) to second(6)) TIMEPROC
from EDWPROD.ETL_Control_Table
WHERE Process_Timestamp in
(sel max(Process_Timestamp)
from EDWPROD.ETL_Control_Table
WHERE Target_Tablename = 'FULFILLMENT_LINE'
)

it returns,

TIMEPROC
-0:29:14.200000
-0:29:14.200000
-0:29:14.200000
-0:29:14.200000
-0:29:14.200000
-0:29:14.200000
-0:29:14.200000
-0:29:14.200000
-0:29:14.200000
-0:29:14.200000

I am not sure why the criteria after that might be causing an issue.

Any help would be appreciated.
Thanks,

15 REPLIES
Enthusiast

Re: Failure 7453 Interval field overflow

I am able to re-produce the error with the below example:

Table vinod_12 has column ts of type timestamp with following records:

ts
=================
2006-05-01 02:14:26
2006-05-03 02:14:26
2006-08-03 02:14:24
2006-08-03 02:14:25
2006-08-03 02:14:26
2006-08-03 02:14:27
2006-10-01 02:14:26

If i execute the below query the subject mentioned error is generated:

sel ((Current_Timestamp - ts) hour(2) to second(6)) TIMEPROC
from vinod_12
WHERE ts in
(sel max(ts)
from vinod_12
)

as the current_timestamp - '2006-10-01 02:14:26' results in 4 digit hour, to overcome this error while casting to interval we need to always specify maximum digits i.e in above query replace hour(2) with hour(4) in above query.

I hope this helps.

~

Enthusiast

Re: Failure 7453 Interval field overflow

I will try and report the errors/results.
Thank you for your help.
Kuldeep
Junior Contributor

Re: Failure 7453 Interval field overflow

Even better, use day(4) to second, this will cover up to 27 years difference :-)

Dieter
Enthusiast

Re: Failure 7453 Interval field overflow

Hi Diter,

Please see the below mentioned issue, we are using Teradata13.10

SELECT ( (TIMESTAMP  '2011-01-17 21:00:00.000000'  - TIMESTAMP'2011-01-10 23:00:00.000000') MINUTE(4))

-- 9960

SELECT ( (TIMESTAMP  '2011-01-17 22:00:00.000000'  - TIMESTAMP'2011-01-10 23:00:00.000000') MINUTE(4))

Error:-Interval field overflow

9999-->It will NOT be exceeded more than 9999

If I want to achieve more than 9999 minutes, what kind of code we need to write, Please help me out from this situation

Thanks in advance  


Junior Contributor

Re: Failure 7453 Interval field overflow

Hi Mahesh,

this is a TD13.10 SQL UDF i wrote for calculating the difference in seconds:

REPLACE FUNCTION TimeStamp_Diff_Seconds
(
ts1 TIMESTAMP(6)
,ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CAST((CAST(ts2 AS DATE AT 0)- CAST(ts1 AS DATE AT 0)) AS DECIMAL(18,6)) * 86400)
+ ((EXTRACT( HOUR FROM ts2) - EXTRACT( HOUR FROM ts1)) * 3600)
+ ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) * 60)
+ (EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1))
;

Dieter

Enthusiast

Re: Failure 7453 Interval field overflow

Dear Diter,

Thanks for your quick reply.

i have been created UDF which you have posted for me, it is giving the results in seconds format as you said.

after that i need to divided by 60, so that exactly it will be given in minutes.

SELECT TimeStamp_Diff_Seconds (TIMESTAMP  '2011-01-10 23:00:00' ,TIMESTAMP '2011-01-17 22:00:00' ) --601200 Second

SELECT 601200/60=10020--MINUTES

Can i have any UDF which will give directly in minutes. OBIEE 11g(Reporting tool ) is accessing objects from td and will be generated the query as per business requirement. If I have UPF which will be given results in minutes.so that I can incorporate this UDF into OBIEE level.So that whenever business users requires for differ in minutes obviously it will be pointed to our UDF (MINUTES). Please help me

thanks for advance

Highlighted
Enthusiast

Re: Failure 7453 Interval field overflow

Hi Diter,

We are awaiting for your valuable response, Any help would be appreciate. 

Enthusiast

Re: Failure 7453 Interval field overflow

Hi Diter,

Finally we could create UDF for MINUTES for same.Please find the logic for sam which will be given in minutes

Let me know if i'm not wrong for same

REPLACE FUNCTION  olap_Dev.TimeStamp_Diff_Minutes

(

   Start_Ts TIMESTAMP(6)

  ,End_Ts TIMESTAMP(6)

)

RETURNS DECIMAL(18,6)

LANGUAGE SQL

CONTAINS SQL

DETERMINISTIC

SQL SECURITY DEFINER

COLLATION INVOKER

INLINE TYPE 1

RETURN

-- Get Days Difference in Minutes (* 24 hours per day * 60 minutes per hour)

(CAST(End_Ts AS DATE) - CAST(Start_Ts AS DATE)) * (24 * 60)

+

-- Get Hours Difference in Minutes ( * 60 minutes per hour)

(EXTRACT(HOUR FROM End_Ts) - EXTRACT(HOUR FROM Start_Ts)) * 60

+

-- Get Minutes Difference

(EXTRACT(MINUTE FROM End_Ts) - EXTRACT(MINUTE FROM Start_Ts));

;

SELECT olap_dev. TimeStamp_Diff_Minutes (TIMESTAMP  '2011-01-17 22:00:00' ,TIMESTAMP '2011-01-17 23:00:00' ) 

Junior Contributor

Re: Failure 7453 Interval field overflow

Hi Mahesh,

if it's correct depends on your definition, between TIMESTAMP  '2011-01-10 23:01:59' and TIMESTAMP  '2011-01-10 23:02:00' there is one second, but the result will be 1 minute, i.e. the same result as "end_ts - start_ts minute".

When you create it based on the seconds calculation you might get:

fractional minutes = timestamp_diff_seconds(start_ts, end_ts) / 60

truncated minutes = cast(timestamp_diff_seconds(start_ts, end_ts) / 60 as bigint)

rounded minutes = cast(timestamp_diff_seconds(start_ts, end_ts) / 60 as decimal(10,0))

You can simple nest this calculation in another UDF instead of copying/modifying the source code.

Btw, i posted a wrong version of the Timestamp_Diff_Seconds, but you already noticed that :-)

This is the correct one:

REPLACE FUNCTION TimeStamp_Diff_Seconds
(
ts1 TIMESTAMP(6)
,ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CAST((CAST(ts2 AS DATE)- CAST(ts1 AS DATE)) AS DECIMAL(18,6)) * 60*60*24)
+ ((EXTRACT( HOUR FROM ts2) - EXTRACT( HOUR FROM ts1)) * 60*60)
+ ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) * 60)
+ (EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1))
;

Dieter