Cast function yielding unrealistic outputs

Database

Cast function yielding unrealistic outputs

Team,

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.


select 

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

from 

   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

Regards,

Deepak C

5 REPLIES
Enthusiast

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

Hi,

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.

Regards,

Deepak C

Enthusiast

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
Enthusiast

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