Numeric Overflow Error

Database

Numeric Overflow Error

Hello All,

Below is my query which is giving the Numeric overflow error on execution. I ran the select query and identified that the column total_week_perc has some records of length 16 where as we have defined the colum datatype as Decimal(4,3). Could you please help me on how to cast/reduce the length of the output to fit the colum datatype length in the query. 

       SELECT
t1.offer_number,
t1.base_product_number_std,
t1.retail_outlet_number,
t1.sold_perc,
t1.total_week_perc,
t1.use_ptgs
FROM
(
SELECT
ptg_table.offer_number,
ptg_table.base_product_number_std,
ptg_table.retail_outlet_number,
((DATE '2013-12-13' - pk.offer_start_date) - CASE WHEN (pk.offer_end_date - pk.offer_start_date < 7) THEN 0 ELSE 1 END) AS target_days,
SUM(ptg_table.daily_perc) AS sold_perc,
ptg_table.total_week_perc,
ptg_table.use_ptgs

FROM DXWI_PROD_PROMOTIONS_PLAY_PEN.promolive_promos_to_report_on rt

INNER JOIN DXWI_PROD_PROMOTIONS_PLAY_PEN.promolive_promotions_key pk
ON pk.offer_number = rt.offer_number
AND pk.base_product_number_std = rt.base_product_number_std

INNER JOIN
( SELECT
pk.offer_number AS offer_number,
pk.base_product_number_std AS base_product_number_std,
ptg.retail_outlet_number AS retail_outlet_number,
pk.offer_start_date AS offer_start_date,
ptg.calendar_date AS calendar_date,
CASE WHEN ptg.daily_percentage > 0.0 AND ptg.daily_percentage < 2.0 THEN CAST(0.02 AS FLOAT)
ELSE CAST(daily_percentage AS FLOAT)/100
END AS daily_perc,
(SUM(daily_perc) OVER (PARTITION BY ptg.retail_outlet_number, pk.base_product_number_std)) AS total_week_perc,
SUM(CASE WHEN daily_perc > 0.00 THEN 1 ELSE 0 END) AS usable_ptg,
(SUM(usable_ptg) OVER (PARTITION BY ptg.retail_outlet_number, pk.base_product_number_std)) AS days_of_ptgs,
CASE WHEN days_of_ptgs = 7 THEN 1 ELSE 0 END AS use_ptgs

FROM DXWI_PROD_PROMOTIONS_PLAY_PEN.promolive_promos_to_report_on rt

INNER JOIN DXWI_PROD_PROMOTIONS_PLAY_PEN.promolive_promotions_key pk
ON pk.offer_number = rt.offer_number
AND pk.base_product_number_std = rt.base_product_number_std

/* translate between bpn_std and bpn */
INNER JOIN DXWI_PROD_PROMOTIONS_PLAY_PEN.promolive_product_data pd
ON pk.base_product_number_std = pd.base_product_number_std

/* get the actual PTGs from the production table */
INNER JOIN DXWI_PROD_VIEW_ACCESS.VWI0PTG_Special_Trade_Perc ptg
ON ptg.base_product_number = pd.base_product_number
AND ptg.calendar_date BETWEEN pk.offer_start_date AND pk.offer_start_date +6

/* only the stores we care about */
INNER JOIN DXWI_PROD_PROMOTIONS_PLAY_PEN.promolive_stores stores
ON ptg.retail_outlet_number = stores.retail_outlet_number

WHERE
rt.mid_week_report = 1

/* if a store stocks the case & MU, we only want one set of PTPs.
we arbitrarily take the PTPs from the product with the smallest base product number */

QUALIFY ROW_NUMBER() OVER( PARTITION BY pk.offer_number,
pk.base_product_number_std,
ptg.retail_outlet_number,
ptg.calendar_date
ORDER BY ptg.base_product_number) = 1

GROUP BY pk.offer_number
,pk.base_product_number_std
,ptg.retail_outlet_number
,pk.offer_start_date
,ptg.calendar_date
,daily_perc
,ptg.base_product_number

) ptg_table

ON pk.offer_number = ptg_table.offer_number
AND pk.base_product_number_std = ptg_table.base_product_number_std
AND ptg_table.calendar_date >= pk.offer_start_date /* ..will only be used for promos of 7 days or less */
AND ptg_table.calendar_date < DATE '2013-12-13'

GROUP BY ptg_table.offer_number,
ptg_table.base_product_number_std,
ptg_table.retail_outlet_number,
ptg_table.total_week_perc,
pk.offer_start_date,
pk.offer_end_date,
ptg_table.use_ptgs

HAVING COUNT(ptg_table.calendar_date) = MAX(target_days)

) t1

Thanks in advance. 

1 REPLY
Enthusiast

Re: Numeric Overflow Error

Hi,

What do you mean by records of length 16? is it a numeric or character value?

Can you please provide a sample value ?

Khurram