All Forums Database
abcreddy 1 post Joined 10/11
13 Dec 2013
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_repo rt_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_repo rt_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. 

ABCReddy
M.Saeed Khurram 544 posts Joined 09/12
13 Dec 2013

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

You must sign in to leave a comment.