Error Text : Numeric Overflow: Search table error ...

former_member606144 · ‎2019 May 07

Team,

I have created a CDS view however when trying for data preview for date range of 4 months it throws following error

When this report is ran for smaller date range (1 month), it works perfectly fine. Could you please suggest whats wrong with the code or changes i need to make to resolve this issue.

@AbapCatalog.sqlViewName: 'ZAPWTESTINGDATA'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'ZA_PW_Testing_Data'
@VDM.viewType: #COMPOSITE
@Analytics.dataCategory: #CUBE
@Analytics.dataExtraction.enabled: true

define view ZA_PW_Testing_Data
with parameters
  @EndUserText.label: 'Enter Start Date'
  P_StartDate                : ersda,
  @EndUserText.label: 'Enter End Date'
  P_EndDate                  : ersda,
  @Consumption.hidden: true 
  @Environment.systemField: #SYSTEM_DATE 
  P_KeyDate : syst_datum ,
  @Consumption.hidden: true 
  @Environment.systemField: #SYSTEM_LANGUAGE 
  P_Language : syst_langu 
    
as select distinct from ZA_PW_INT (P_KeyDate: $parameters.P_KeyDate, P_Language: $parameters.P_Language)
join ZA_PW_Test (P_StartDate: $parameters.P_StartDate, P_EndDate: $parameters.P_EndDate)
    on ZA_PW_INT.PRODORDER = ZA_PW_Test.aufnr
join ZA_PW_Mat (P_KeyDate: $parameters.P_KeyDate, P_Language: $parameters.P_Language)
    on ZA_PW_Mat.AUFNR = ZA_PW_INT.PRODORDER
join ZA_PW_RESB_MBEW (P_KeyDate: $parameters.P_KeyDate, P_Language: $parameters.P_Language) on ZA_PW_Mat.Material = ZA_PW_RESB_MBEW.Material and ZA_PW_Mat.AUFNR = ZA_PW_RESB_MBEW.AUFNR
join ZA_PW_JEST on ZA_PW_INT.PRODORDER = ZA_PW_JEST.aufnr
{
key ZA_PW_INT.PRODORDER, 
[email protected] : {selectionType: #SINGLE, multipleSelections:false, mandatory: true }
ZA_PW_INT.Plant, 
ZA_PW_INT.Department, 
ZA_PW_INT.WORKCENTERGROUP, 
ZA_PW_INT.WC, 
ZA_PW_INT.ZZ_OEE_RATE,
ZA_PW_INT.ZZ_OEE_UOM,
ZA_PW_JEST.status,
 
ZA_PW_Mat.Material, 
ZA_PW_Mat.MaterialDescription, 
ZA_PW_Mat.MaterialBaseUnit, 
ZA_PW_Mat.MaterialType, 
ZA_PW_Mat.MaterialGroup, 
ZA_PW_Mat.ZFIN_PH4,
ZA_PW_Mat.OEE_QTY_UOM,
ZA_PW_INT.ORDERQTY,
ZA_PW_INT.PRODQTY,
ZA_PW_Mat.PROD_LBS, 
ZA_PW_Mat.bdmng, 
ZA_PW_Mat.ausch,
ZA_PW_Mat.STD_MAT_QTY,

@DefaultAggregation:#SUM
ZA_PW_INT.STD_SETUP_HR as STD_SETUP_HR, 
@DefaultAggregation:#SUM
ZA_PW_INT.STD_RUN_HR as STD_RUN_HR, 
@DefaultAggregation:#SUM
ZA_PW_INT.STD_SCH_HR  as STD_SCH_HR, 

@DefaultAggregation:#SUM
case when (ZA_PW_INT.STD_RUN_HR = 0) then 0 else
division(ZA_PW_INT.PRODQTY,ZA_PW_INT.STD_RUN_HR,1) end as STD_UOM_RUN_HR,

@DefaultAggregation:#SUM
case when (ZA_PW_INT.STD_SCH_HR = 0) then 0 else
division(ZA_PW_INT.PRODQTY,ZA_PW_INT.STD_SCH_HR,1) end as STD_UOM_SCH_HR,

@DefaultAggregation:#SUM
case when (ZA_PW_INT.STD_RUN_HR = 0) then 0 else
division(ZA_PW_Mat.PROD_LBS,ZA_PW_INT.STD_RUN_HR,1) end as STD_LB_RUN_HR,

@DefaultAggregation:#SUM
case when (ZA_PW_INT.STD_SCH_HR = 0) then 0 else
division(ZA_PW_Mat.PROD_LBS,ZA_PW_INT.STD_SCH_HR,1) end as STD_LB_SCH_HR,

@DefaultAggregation:#SUM
ZA_PW_Test.ISM01 as ACT_SETUP_HR,
@DefaultAggregation:#SUM
ZA_PW_Test.ISM02 as ACT_RUN_HR,
@DefaultAggregation:#SUM
ZA_PW_Test.ISM03 as ACT_DT_HR,
@DefaultAggregation:#SUM
(ZA_PW_Test.ISM01+ZA_PW_Test.ISM02+ZA_PW_Test.ISM03) as ACT_SCH_HR,

@DefaultAggregation:#SUM
case when (ZA_PW_Test.ISM02 = 0) then 0 else
division(ZA_PW_INT.PRODQTY,ZA_PW_Test.ISM02,1) end as ACT_UOM_RUN_HR,

@DefaultAggregation:#SUM
case when (ZA_PW_Test.ISM01 = 0 or ZA_PW_Test.ISM02 = 0 or ZA_PW_Test.ISM03 = 0)  then 0 else
division(ZA_PW_INT.PRODQTY,(ZA_PW_Test.ISM01+ZA_PW_Test.ISM02+ZA_PW_Test.ISM03),1) end as ACT_UOM_SCH_HR,

@DefaultAggregation:#SUM
case when (ZA_PW_Test.ISM01 = 0) then 0 else
division(ZA_PW_Mat.PROD_LBS,ZA_PW_Test.ISM01,1) end as ACT_LB_RUN_HR,

@DefaultAggregation:#SUM
case when (ZA_PW_Test.ISM01 = 0 or ZA_PW_Test.ISM02 = 0 or ZA_PW_Test.ISM03 = 0)  then 0 else
division(ZA_PW_Mat.PROD_LBS,(ZA_PW_Test.ISM01+ZA_PW_Test.ISM02+ZA_PW_Test.ISM03),1) end as ACT_LB_SCH_HR,

@DefaultAggregation:#SUM
case when (ZA_PW_Test.ISM02 = 0 or ZA_PW_INT.STD_RUN_HR = 0)  then 0 else
division(division(ZA_PW_INT.PRODQTY,ZA_PW_Test.ISM02,1),division(ZA_PW_INT.PRODQTY,ZA_PW_INT.STD_RUN_HR,1),1) end as PERF_TO_STD_RUN,

@DefaultAggregation:#SUM
case when (ZA_PW_Test.ISM01 = 0 or ZA_PW_Test.ISM02 = 0 or ZA_PW_Test.ISM03 = 0 or ZA_PW_INT.STD_SCH_HR = 0)  then 0 else
division(division(ZA_PW_INT.PRODQTY,(ZA_PW_Test.ISM01+ZA_PW_Test.ISM02+ZA_PW_Test.ISM03),1),division(ZA_PW_INT.PRODQTY,ZA_PW_INT.STD_SCH_HR,1),1) end as PERF_TO_STD_SCH,

@DefaultAggregation:#SUM
case when (ZA_PW_Test.ISM01 = 0 or ZA_PW_Test.ISM02 = 0 or ZA_PW_Test.ISM03 = 0)  then 0 else
division(ZA_PW_Test.ISM02*100,(ZA_PW_Test.ISM01+ZA_PW_Test.ISM02+ZA_PW_Test.ISM03),1) end as Availability_Percentage,
---------
@DefaultAggregation:#SUM
(ZA_PW_RESB_MBEW.STD_MAT_bdmng - ZA_PW_RESB_MBEW.STD_ACT_ENMNG) as Variance_MAT,

@DefaultAggregation:#SUM
ZA_PW_RESB_MBEW.STD_MAT_bdmng as STD_MAT_BOM,
@DefaultAggregation:#SUM
ZA_PW_RESB_MBEW.STD_ACT_ENMNG as STD_ACT_BOM,

@DefaultAggregation:#SUM
case when (ZA_PW_INT.PRODQTY = 0) then 0 else
division(ZA_PW_RESB_MBEW.STD_MAT_bdmng,ZA_PW_INT.PRODQTY,1) end as STD_COST_UNIT,

@DefaultAggregation:#SUM
case when (ZA_PW_INT.PRODQTY = 0) then 0 else
division(ZA_PW_RESB_MBEW.STD_ACT_ENMNG,ZA_PW_INT.PRODQTY,1) end as ACT_COST_UNIT,

ZA_PW_Mat.ACT_MAT_QTY,
ZA_PW_Mat.OPER_SCRAP_LBS,


ZA_PW_INT.PLAN_START as PLAN_START, 
ZA_PW_INT.ACT_START, 
ZA_PW_INT.PLAN_FINISH, 
ZA_PW_INT.ACT_FINISH,
 
ZA_PW_Test.zdowntime_operator, 
ZA_PW_Test.zdowntime_material, 
ZA_PW_Test.zdowntime_webbreaks, 
ZA_PW_Test.zdowntime_quality, 
ZA_PW_Test.zdowntime_operations, 
ZA_PW_Test.zdowntime_maintenance
}

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Error Text : Numeric Overflow: Search table error [6994] exception 70006994