7453 : Interval Field Overflow issue

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Tourist

7453 : Interval Field Overflow issue

This is the view defination. when we are performing the below query, it is showing the error interval field overflow. Please suggest

Select * from dp_vew.REM_WO

 

sel
a.Work_Order_ID
,a.Contact_Number__FL
,a.FDN__FL
,a.Submit_Date
,a.Completed_Date
,a.Status
,a.Work_Order_Type
,a.Summary_S
,a.Nawras_Building_Code__FL
,a.Region
,a.Wilaya
,a.Locality
,a.Flat_Number
,a.Building_Code
,a.Bulding_Number
,a.Building_Area
,a.Service_ID
,a.CPE_Serial


,obc_last_status.DRAFT_RECIEVED_DATE as OBC_Draft_Rec_Date
,obc_last_status.SUBMIT_DATE as OBC_Submit_Date
,obc_last_status.COMPLETE_DATE as OBC_Complete_Date
,obc_last_status.FDN OBC_FDN
,obc_last_status.CONTACT_NUMBER OBC_Contact_Number
,obc_last_status.WAY_NUMBER as OBC_WAY_Number
,obc_last_status.HOUSE_NUMBER as OBC_House_Number
,obc_last_status.CUSTOMER_NAME as OBC_Customer_Name
,obc_last_status.PROPERTY_TYPE as OBC_Property_Type
,obc_last_status.PRODUCT_TYPE as OBC_Product_Type
,obc_last_status.CUSTOMER_EMAIL as OBC_Customer_Email
,obc_last_status.WILAYA as OBC_Wilaya
,obc_last_status.AREA as OBC_AREA
,obc_last_status.SUPPORT_GROUP as OBC_Support_Group
,obc_last_status.ASSIGNEE_GROUPS as OBC_Assignee_Groups

,obc_last_status.OBC_STATUS as Last_OBC_Status
,obc_last_status.MODIFIED_DATE as Last_OBC_Modified_Date

,CASE WHEN obc_OA.work_order_id is not null THEN 'Y' ELSE NULL END as Order_Assigned_Flag
,CASE WHEN obc_SA.work_order_id is not null THEN 'Y' ELSE NULL END as Survey_Assigned_Flag
,CASE WHEN obc_ITC.work_order_id is not null THEN 'Y' ELSE NULL END as Inst_Tested_Complete_Flag
,CASE WHEN obc_PC.work_order_id is not null THEN 'Y' ELSE NULL END as Problematic_Order_Flag
,CASE WHEN obc_OR.work_order_id is not null THEN 'Y' ELSE NULL END as Order_Rejected_Flag
,CASE WHEN obc_OC.work_order_id is not null THEN 'Y' ELSE NULL END as Order_Cancel_Flag
,CASE WHEN obc_ONT.work_order_id is not null THEN 'Y' ELSE NULL END as ONT_Activated_Flag
,CASE WHEN obc_RNBS.work_order_id is not null THEN 'Y' ELSE NULL END as RL_Notified_Billing_Start_Flag
,CASE WHEN obc_C.work_order_id is not null THEN 'Y' ELSE NULL END as Closed

,coalesce(obc_OA.Status_Active_Hours, 0) as Order_Assigned_Hours
,coalesce(obc_SA.Status_Active_Hours, 0) as Survey_Assigned_Hours
,coalesce(obc_ITC.Status_Active_Hours, 0) as Inst_Tested_Complete_Hours
,coalesce(obc_PC.Status_Active_Hours, 0) as Problematic_Order_Hours
,coalesce(obc_OR.Status_Active_Hours, 0) as Order_Rejected_Hours
,coalesce(obc_OC.Status_Active_Hours, 0) as Order_Cancel_Hours
,coalesce(obc_ONT.Status_Active_Hours, 0) as ONT_Activated_Hours
,coalesce(obc_RNBS.Status_Active_Hours, 0) as RL_Notified_Bill_Start_Hours
,coalesce(obc_C.Status_Active_Hours, 0) as Closed_Hours

 

,CASE WHEN obc_OA.note like '%MDU%' OR obc_SA.note like '%MDU%'
OR obc_ITC.note like '%MDU%'
OR obc_PC.note like '%MDU%'
OR obc_OR.note like '%MDU%'
OR obc_OC.note like '%MDU%'
OR obc_ONT.note like '%MDU%'
OR obc_RNBS.note like '%MDU%'
OR obc_C.note like '%MDU%'
THEN 'MDU' ELSE 'SDU'
END as MDU_SDU

,CASE WHEN obc_OA.note like '%NOC%' THEN obc_OA.Note
WHEN obc_SA.note like '%NOC%' THEN obc_SA.Note
WHEN obc_ITC.note like '%NOC%' THEN obc_ITC.Note
WHEN obc_PC.note like '%NOC%' THEN obc_PC.Note
WHEN obc_OR.note like '%NOC%' THEN obc_OR.Note
WHEN obc_OC.note like '%NOC%' THEN obc_OC.Note
WHEN obc_ONT.note like '%NOC%' THEN obc_ONT.Note
WHEN obc_RNBS.note like '%NOC%' THEN obc_RNBS.Note
WHEN obc_C.note like '%NOC%' THEN obc_C.Note
ELSE NULL END as NOC_Status

,CASE WHEN obc_OA.note like '%Power level%' THEN obc_OA.Note
WHEN obc_SA.note like '%Power level%' THEN obc_SA.Note
WHEN obc_ITC.note like '%Power level%' THEN obc_ITC.Note
WHEN obc_PC.note like '%Power level%' THEN obc_PC.Note
WHEN obc_OR.note like '%Power level%' THEN obc_OR.Note
WHEN obc_OC.note like '%Power level%' THEN obc_OC.Note
WHEN obc_ONT.note like '%Power level%' THEN obc_ONT.Note
WHEN obc_RNBS.note like '%Power level%' THEN obc_RNBS.Note
WHEN obc_C.note like '%Power level%' THEN obc_C.Note
ELSE NULL END as Power_Level

,CASE WHEN obc_OA.note like '%Rejection reason%' THEN obc_OA.Note
WHEN obc_SA.note like '%Rejection reason%' THEN obc_SA.Note
WHEN obc_ITC.note like '%Rejection reason%' THEN obc_ITC.Note
WHEN obc_PC.note like '%Rejection reason%' THEN obc_PC.Note
WHEN obc_OR.note like '%Rejection reason%' THEN obc_OR.Note
WHEN obc_OC.note like '%Rejection reason%' THEN obc_OC.Note
WHEN obc_ONT.note like '%Rejection reason%' THEN obc_ONT.Note
WHEN obc_RNBS.note like '%Rejection reason%' THEN obc_RNBS.Note
WHEN obc_C.note like '%Rejection reason%' THEN obc_C.Note
ELSE NULL END as Rejection_Reason


,CASE WHEN obc_OA.note like '%Survey Assigned%' THEN obc_OA.Note
WHEN obc_SA.note like '%Survey Assigned%' THEN obc_SA.Note
WHEN obc_ITC.note like '%Survey Assigned%' THEN obc_ITC.Note
WHEN obc_PC.note like '%Survey Assigned%' THEN obc_PC.Note
WHEN obc_OR.note like '%Survey Assigned%' THEN obc_OR.Note
WHEN obc_OC.note like '%Survey Assigned%' THEN obc_OC.Note
WHEN obc_ONT.note like '%Survey Assigned%' THEN obc_ONT.Note
WHEN obc_RNBS.note like '%Survey Assigned%' THEN obc_RNBS.Note
WHEN obc_C.note like '%Survey Assigned%' THEN obc_C.Note
ELSE NULL END as Survey_Assigned


,CASE WHEN obc_OA.note like '%Category%' THEN obc_OA.Note
WHEN obc_SA.note like '%Category%' THEN obc_SA.Note
WHEN obc_ITC.note like '%Category%' THEN obc_ITC.Note
WHEN obc_PC.note like '%Category%' THEN obc_PC.Note
WHEN obc_OR.note like '%Category%' THEN obc_OR.Note
WHEN obc_OC.note like '%Category%' THEN obc_OC.Note
WHEN obc_ONT.note like '%Category%' THEN obc_ONT.Note
WHEN obc_RNBS.note like '%Category%' THEN obc_RNBS.Note
WHEN obc_C.note like '%Category%' THEN obc_C.Note
ELSE NULL END as Category

,CASE WHEN obc_OA.note like '%Installation Arranged%' THEN obc_OA.Note
WHEN obc_SA.note like '%Installation Arranged%' THEN obc_SA.Note
WHEN obc_ITC.note like '%Installation Arranged%' THEN obc_ITC.Note
WHEN obc_PC.note like '%Installation Arranged%' THEN obc_PC.Note
WHEN obc_OR.note like '%Installation Arranged%' THEN obc_OR.Note
WHEN obc_OC.note like '%Installation Arranged%' THEN obc_OC.Note
WHEN obc_ONT.note like '%Installation Arranged%' THEN obc_ONT.Note
WHEN obc_RNBS.note like '%Installation Arranged%' THEN obc_RNBS.Note
WHEN obc_C.note like '%Installation Arranged%' THEN obc_C.Note
ELSE NULL END as Installation_Arranged

,CASE WHEN obc_OA.note like '%RL issue%' THEN obc_OA.Note
WHEN obc_SA.note like '%RL issue%' THEN obc_SA.Note
WHEN obc_ITC.note like '%RL issue%' THEN obc_ITC.Note
WHEN obc_PC.note like '%RL issue%' THEN obc_PC.Note
WHEN obc_OR.note like '%RL issue%' THEN obc_OR.Note
WHEN obc_OC.note like '%RL issue%' THEN obc_OC.Note
WHEN obc_ONT.note like '%RL issue%' THEN obc_ONT.Note
WHEN obc_RNBS.note like '%RL issue%' THEN obc_RNBS.Note
WHEN obc_C.note like '%RL issue%' THEN obc_C.Note
ELSE NULL END as RL_Issue

 

,(cast( coalesce(a.completed_date, current_date) as date)
- cast(a.submit_date as date) ) as total_days
, 1 as WO_Count
,obc_last_status.NUMBER_OF_DAYS as LAST_OBC_No_Of_Days

 

 

 

 

--sel *
from dp_vew.REM_DwhFLConsWO a
left outer join
(
select * from dp_vew.REM_DWHSF b where status_end_date is null
) as obc_last_status
on a.work_order_id = obc_last_status.work_order_id


left outer join
(
sel work_order_id, count(*) as cnt
, sum( extract(hour from (coalesce(status_end_date, current_timestamp) - modified_date Hour(4) TO second) )) as Status_Active_Hours
, max(Note) as note
from
dp_vew.REM_DWHSF
where obc_status = 'Order Accepted'
group by 1
)
as obc_OA
on a.work_order_id = obc_OA.work_order_id

left outer join
(
sel work_order_id, count(*) as cnt
, sum( extract(hour from (coalesce(status_end_date, current_timestamp) - modified_date Hour(4) TO second) )) as Status_Active_Hours
, max(Note) as note
from
dp_vew.REM_DWHSF
where obc_status = 'Survey Assigned'
group by 1
)
as obc_SA
on a.work_order_id = obc_SA.work_order_id

left outer join
(
sel work_order_id, count(*) as cnt
, sum( extract(hour from (coalesce(status_end_date, current_timestamp) - modified_date Hour(4) TO second) )) as Status_Active_Hours
, max(Note) as note
from
dp_vew.REM_DWHSF
where obc_status = 'Installation Tested and Complete'
group by 1
)
as obc_ITC
on a.work_order_id = obc_ITC.work_order_id

left outer join
(
sel work_order_id, count(*) as cnt
, sum( extract(hour from (coalesce(status_end_date, current_timestamp) - modified_date Hour(4) TO second) )) as Status_Active_Hours
, max(Note) as note
from
dp_vew.REM_DWHSF
where obc_status = 'Problematic Order'
group by 1
)
as obc_PC
on a.work_order_id = obc_PC.work_order_id

left outer join
(
sel work_order_id, count(*) as cnt
, sum( extract(hour from (coalesce(status_end_date, current_timestamp) - modified_date Hour(4) TO second) )) as Status_Active_Hours
, max(Note) as note
from
dp_vew.REM_DWHSF
where obc_status = 'Order Rejected'
group by 1
)
as obc_OR
on a.work_order_id = obc_OR.work_order_id

left outer join
(
sel work_order_id, count(*) as cnt
, sum( extract(hour from (coalesce(status_end_date, current_timestamp) - modified_date Hour(4) TO second) )) as Status_Active_Hours
, max(Note) as note
from
dp_vew.REM_DWHSF
where obc_status = 'Order Cancel'
group by 1
)
as obc_OC
on a.work_order_id = obc_OC.work_order_id

left outer join
(
sel work_order_id, count(*) as cnt
, sum( extract(hour from (coalesce(status_end_date, current_timestamp) - modified_date Hour(4) TO second) )) as Status_Active_Hours
, max(Note) as note
from
dp_vew.REM_DWHSF
where obc_status = 'ONT Activated'
group by 1
)
as obc_ONT
on a.work_order_id = obc_ONT.work_order_id

left outer join
(
sel work_order_id, count(*) as cnt
, sum( extract(hour from (coalesce(status_end_date, current_timestamp) - modified_date Hour(4) TO second) )) as Status_Active_Hours
, max(Note) as note
from
dp_vew.REM_DWHSF
where obc_status = 'RL Notified Billing Start'
group by 1
)
as obc_RNBS
on a.work_order_id = obc_RNBS.work_order_id

left outer join
(
sel work_order_id, count(*) as cnt
, sum( extract(hour from (coalesce(status_end_date, current_timestamp) - modified_date Hour(4) TO second) )) as Status_Active_Hours
, max(Note) as note
from
dp_vew.REM_DWHSF
where obc_status = 'Closed'
group by 1
)
as obc_C
on a.work_order_id = obc_C.work_order_id

Rahul Satija
1 REPLY
Teradata Employee

Re: 7453 : Interval Field Overflow issue

Apparently you have some differences that exceed the 9999 hours 59 minutes 59.999999 seconds limit for the INTERVAL HOUR(4) TO SECOND type.

 

A SQL UDF like the one in this post from @DaveWellman would be helpful, or you could incorporate similar calculations directly in the view.