Cast function yielding unrealistic outputs


Cast function yielding unrealistic outputs


I am new to teradata and I have written a simple query to extract sales and budget information from two tables. Initially the query resulted in the error 2616 - numeric overflow, post reading I have added the cast function. 

The numbers from the output is stageringly high than what it is actually supposed to be, can you please assist in resolving this query.


a.Year_Week_Number as FW,

sum(cast(a.sales_value_ty as decimal(4,0))) as sales,

sum(cast(b.sales as decimal(4,0))) as budget,

sum(cast(a.sales_volume_ty as decimal(4,0))) as vol


   DXWV_PROD_US_PLAY_PEN.NW36_Dept_Catgeory_data a inner join DXWV_PROD_US_PLAY_PEN.NW36_Category_Budget b  

on a.Year_Week_Number= b.YWN 

where a.Year_Week_Number  = 201507

group by 1


Deepak C


Re: Cast function yielding unrealistic outputs

Can u please post a sample example so that it is more clear?

Re: Cast function yielding unrealistic outputs


If the query runs as expected, I am expecting the results as shown below 

FW  Sales  Budget Vol
201507  $        13,869,499  $            11,706,354                               5,010,852

however I am getting the results as below

FW  Sales  Budget Vol
201507  $      190,428,288  $      2,774,403,528                             64,756,723

Hope this helps.


Deepak C


Re: Cast function yielding unrealistic outputs

Can u also please provide data in the source tables as well

Re: Cast function yielding unrealistic outputs

DXWV_PROD_US_PLAY_PEN.NW36_Dept_Catgeory_data a

Year_Week_Number Class_name Trading_Director Category_Manager Sales_Value_TY Sales_Volume_TY
201507 Magazines/Books NON-FOODS General Merchandise 4342 794
201507 Specialty Yogurt FRESH/PERISHABLE Dairy 30918 14812
201507 Hair Care NON-FOODS Health and Beauty Care 13617 2944
201507 Cooking Greens FRESH/PERISHABLE Fresh Produce 6776 4771
201507 Dips/Spreads FRESH/PREPARED Prepared Food 123685 35179
201507 Cookies/Sweets FRESH/PREPARED Bakery 33790 12365
201507 Burritos FRESH/PREPARED Prepared Food 34264 9248
201507 Cleaners NON-FOODS Non-Food/Household 11038 2777
201507 Oil/Vinegar/Coatings PACKAGED FOOD Grocery 51519 10339
201507 Dough Products FRESH/PERISHABLE Dairy 33108 11532
201507 Red Wine PACKAGED BEVERAGES Alcoholic Beverages 117497 13743
201507 Olives/Pickles/Peppers PACKAGED FOOD Grocery 23039 9089
201507 Fruit Snacks PACKAGED FOOD Snacks 25198 13498

Re: Cast function yielding unrealistic outputs

Do you have 201507 multiple times in NW36_Category_Budget table? If so, the join you have is causing multiple records for same year week and in turn your sum gets shooted up.

Make sure you have only a one to one join