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 ), ...;