Failure 7453 Interval field overflow

Analytics

Re: Failure 7453 Interval field overflow

Never used these SQL Functions before.  Just tried your difference between Timestamps Function - love it !

This is my New Year's Resolution : create some SQL Functions !

Happy New Year, guys

Re: Failure 7453 Interval field overflow

Please help with this query. Which place is the interval overflowing? I am not able to correct this after several attempts.

select

 

x.str_nbr,

x.year_of_calendar,

x.week_of_year,

 

sum(case when rcvd_to_ready between 0 and 15 then 1 else 0 end) as pre_promised_w,

sum(case when rcvd_to_sold between 0 and 25 then 1 else 0 end) as pre_waiters,

sum(case when rcvd_to_sold between 0 and 25 then rcvd_to_ready else 0 end) as pre_waiters_time

 

from

 

(

SELECT

    sta.rx_nbr

    ,sta.str_nbr

    ,sta.dspn_fill_nbr

    ,sta.fill_type_cd

    ,sta.year_of_calendar,

        sta.week_of_year,

        CAST(sta.fill_enter_dttm AS DATE) AS fill_enter_dt

    ,CASE   WHEN sta.dspn_fill_nbr = '1' AND sta.fill_type_cd = 'N' THEN EXTRACT(DAY FROM ((sta.fill_vrfy_dttm - sta.scan_dttm) DAY(4) TO MINUTE))*24*60 + EXTRACT(HOUR FROM ((sta.fill_vrfy_dttm - sta.scan_dttm) DAY(4) TO MINUTE))*60 + EXTRACT(MINUTE FROM ((sta.fill_vrfy_dttm - sta.scan_dttm) DAY(4) TO MINUTE))

            WHEN (sta.dspn_fill_nbr = '1' AND sta.fill_type_cd = 'C') OR (sta.dspn_fill_nbr > 1 AND sta.fill_type_cd = 'R') THEN EXTRACT(DAY FROM ((sta.fill_vrfy_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))*24*60 + EXTRACT(HOUR FROM ((sta.fill_vrfy_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))*60 + EXTRACT(MINUTE FROM ((sta.fill_vrfy_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))

            ELSE NULL END AS rcvd_to_ready

    ,CASE   WHEN sta.dspn_fill_nbr = '1' AND sta.fill_type_cd = 'N' THEN EXTRACT(DAY FROM ((sta.fill_sold_dttm - sta.scan_dttm) DAY(4) TO MINUTE))*24*60 + EXTRACT(HOUR FROM ((sta.fill_sold_dttm - sta.scan_dttm) DAY(4) TO MINUTE))*60 + EXTRACT(MINUTE FROM ((sta.fill_sold_dttm - sta.scan_dttm) DAY(4) TO MINUTE))

            WHEN (sta.dspn_fill_nbr = '1' AND sta.fill_type_cd = 'C') OR (sta.dspn_fill_nbr > 1 AND sta.fill_type_cd = 'R') THEN EXTRACT(DAY FROM ((sta.fill_sold_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))*24*60 + EXTRACT(HOUR FROM ((sta.fill_sold_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))*60 + EXTRACT(MINUTE FROM ((sta.fill_sold_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))

            ELSE NULL END AS rcvd_to_sold

FROM

(

    SELECT

        pf.rx_nbr

        ,pf.str_nbr

        ,pf.dspn_fill_nbr

        ,pf.fill_type_cd

        ,n.year_of_calendar,

        n.week_of_year,

       

        

        MIN(CASE WHEN (p.scan_tm IS NULL AND p.pbr_ord_nbr IS NOT NULL) THEN e.erx_msg_rcvd_dttm ELSE CAST(p.scan_dt AS TIMESTAMP(0)) + (p.scan_tm - TIME '00:00:00' HOUR TO SECOND) END) AS scan_dttm

        ,MIN(CAST(pf.fill_enter_dt AS TIMESTAMP(0)) + (pf.fill_enter_tm - TIME '00:00:00' HOUR TO SECOND)) AS fill_enter_dttm

        ,MIN(CAST(pf.fill_data_review_dt AS TIMESTAMP(0)) + (pf.fill_data_review_tm - TIME '00:00:00' HOUR TO SECOND)) AS data_review_dttm

        ,MIN(CAST(pf.fill_print_dt AS TIMESTAMP(0)) + (pf.fill_print_tm - TIME '00:00:00' HOUR TO SECOND)) AS fill_print_dttm

        ,MIN(CAST(pf.filling_dt AS TIMESTAMP(0)) + (pf.filling_tm - TIME '00:00:00' HOUR TO SECOND)) AS fill_dttm

        ,MIN(CAST(pf.fill_vrfy_dt AS TIMESTAMP(0)) + (pf.fill_vrfy_tm - TIME '00:00:00' HOUR TO SECOND)) AS fill_vrfy_dttm

        ,MIN(CAST(pf.fill_sold_dt AS TIMESTAMP(0)) + (pf.fill_sold_tm - TIME '00:00:00' HOUR TO SECOND)) AS fill_sold_dttm

    FROM

        prdedwvwh.prescription_fill AS pf

    JOIN

        (

        SELECT

            pfs.rx_nbr

            ,pfs.str_nbr

            ,pfs.rx_create_dt

            ,pfs.dspn_fill_nbr

            ,sys.year_of_calendar,

                sys.week_of_year

        FROM

            prdedwvwh.prescription_fill_sold AS pfs

 

             inner join sys_calendar.calendar sys

                                 on pfs.fill_sold_dt=sys.calendar_date

 

                 and TO_CHAR(pfs.fill_sold_dt,'YYYYMMDD') BETWEEN '20150628' and '20150704'

                 

        ) AS n

    ON

        pf.rx_nbr = n.rx_nbr

        AND

        pf.str_nbr = n.str_nbr

        AND

        pf.rx_create_dt = n.rx_create_dt

        AND

        pf.dspn_fill_nbr = n.dspn_fill_nbr

    JOIN

        prdedwvwh.prescription AS p

    ON

        pf.rx_nbr = p.rx_nbr

        AND

        pf.str_nbr = p.str_nbr

        AND

        pf.rx_create_dt = p.rx_create_dt

    LEFT JOIN

        prdedwvwh.prescription_erx_msg_map AS e

    ON

        e.rx_nbr = pf.rx_nbr

        AND

        e.str_nbr = pf.str_nbr

        AND

        e.rx_create_dt = pf.rx_create_dt

    GROUP BY

        pf.rx_nbr

        ,pf.str_nbr

        ,pf.dspn_fill_nbr

        ,pf.fill_type_cd

        ,n.year_of_calendar,

        n.week_of_year

    ) AS sta

    group by 1,2,3,4,5,6,7,8,9

    ) x

        

    where rcvd_to_sold between 0 and 25

   

    group by 1,2,3

Re: Failure 7453 Interval field overflow

I have been looking for an easy way to calculate the difference in seconds between two timestamps.   The TimeSTamp_Diff_Seconds function posted by Dieter on 22MAR2013 works great.

Enthusiast

Re: Failure 7453 Interval field overflow

Hi Friends,

We've a scenario of subtracting two timestamp fields in a procedure. The scenario is like below.

We've set a variable value as follows.

SET v_diff_tm= current_timestamp(3);

SET v_debug_message = sp_debug( CAST( (CURRENT_TIMESTAMP(1) (FORMAT 'HH:MI:SS') ) AS CHAR(21)) || ',' || CAST(((CURRENT_TIMESTAMP(0) - v_diff_tm) SECOND(3))AS CHAR(21))

Our procedure is executing successfully from the past one year in production but suddenly it has stared failing with 'Interval Field Overflow' issue at above statement.

1.Can you please let us know what could be the reason for this sudden failure.

2.We've identified it has failed due to the above casting operation to SECOND(3), the returned seconds value is more than the given range. What can be the fix to overcome this issue.As the max value for INTERVAL SECOND is 4 ,can we change the above INTERVAL from SECOND(3) to SECOND(4).

Will it solve the problem permenantly? We need your help at the earliest. Thanks in advance !!

Regards,

Subbu.

Enthusiast

Re: Failure 7453 Interval field overflow

Can we use the function inside a select query.

 

ex: select col1, col12, function from table joing tab2 on a = b;


CarlosBarichell wrote:

I have been looking for an easy way to calculate the difference in seconds between two timestamps.   The TimeSTamp_Diff_Seconds function posted by Dieter on 22MAR2013 works great.



CarlosBarichell wrote:

I have been looking for an easy way to calculate the difference in seconds between two timestamps.   The TimeSTamp_Diff_Seconds function posted by Dieter on 22MAR2013 works great.



CarlosBarichell wrote:

I have been looking for an easy way to calculate the difference in seconds between two timestamps.   The TimeSTamp_Diff_Seconds function posted by Dieter on 22MAR2013 works great.



CarlosBarichell wrote:

I have been looking for an easy way to calculate the difference in seconds between two timestamps.   The TimeSTamp_Diff_Seconds function posted by Dieter on 22MAR2013 works great.


 

Teradata Employee

Re: Failure 7453 Interval field overflow

Absolutely.  Select ..., Timestamp_Diff_Seconds( TS1, TS2 ), ...;

Enthusiast

Re: Failure 7453 Interval field overflow

Hello,

Attempting to use the correct** version of TimeStamp_Diff_Seconds but receiving "[5407] Invalid operation for DateTime or Interval". Attempting with two TIMESTAMP(6) fields. Using TD Version 15.10 

Examples:

StartDateTime 8/10/2018 11:05:13.000000

StopDateTime 8/10/2018 11:08:10.000000

Expecting the same output I would get with below code (177.000000)

(StopDateTime - StartDateTime second(4,6))

 Main problem with using "minus" is the interval overflow error when going over > 9999 seconds. 

 

             >>>Any suggestion on a work around?

 

**By the CORRECT version I meant:

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))

;

Enthusiast

Re: Failure 7453 Interval field overflow

Attempting to use the correct** version of TimeStamp_Diff_Seconds but receiving "[5407] Invalid operation for DateTime or Interval". Attempting with two TIMESTAMP(6) fields. Using TD Version 15.10 

Examples:

StartDateTime 8/10/2018 11:05:13.000000

StopDateTime 8/10/2018 11:08:10.000000

Expecting the same output I would get with below code (177.000000)

(StopDateTime - StartDateTime second(4,6))

 Main problem with using "minus" is the interval overflow error when going over > 9999 seconds. 

 

             >>>Any suggestion on a work around?

 

**By the CORRECT version I meant:

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))

;

Tags (1)
Junior Contributor

Re: Failure 7453 Interval field overflow

SELECT
   TIMESTAMP '2018-08-10 11:05:13.000000' AS StartDateTime,
   TIMESTAMP '2018-08-10 11:08:10.000000' AS StopDateTime,
   (StopDateTime - StartDateTime SECOND(4,6)),
   TimeStamp_Diff_Seconds(StartDateTime, StopDateTime)

returns the correct result for me.

 

Enthusiast

Re: Failure 7453 Interval field overflow

Thank you, I looked over the code and found one discrepancy on my side. The UDF does work as you mentioned.

Thank you!