CCSlice,

What's the datatype of the column you are summing?

Your error is a numeric overflow so the resulting type column of your calculation is not able to hold the results.

You can use the Type function to help determine where the overflow is taking place.

Rglass

RGLASS, THE DATATYPE OF THE COLUMN IS VARCHAR.

No I mean the numeric column you are aggregating.

There is not a numeric column being aggregated. I am summing based upon the conditions being satisfied in the CASE statement.

I would suggest you check out

SQL Functions, Operators,

Expressions, and Predicates any release.

--SUM is valid only for numeric data.

Are you sure it's not caused by the DASHBOARD_REPORT?

This should work as-is, SUM(1) results in an INTEGER, you just might simplify it a bit removing the CASTs as **1.00** results in a DECIMAL:

SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'C001' AND WKFLW_STS_NM IN ('RELEASED', 'HELD') THEN 1.00 ELSE 0 END)/ NULLIFZERO(SUM(CASE WHEN PRODUCT_NM IN('INVENTORY') AND SRC_ORG_UNT_CD = 'CAPE' THEN 1.00 ELSE 0 END)) AS "TRA-0001"

Did you try to run it with a single SUM to check if this fails, too?

Dieter

Thanks Dieter. Dashboard Report is the CTE and there are no errors there. I cannot test a single Sum to check because I have 11 products (columns) x 9 key performance indicators (rows) that I need to return. So to do this I am performing the calculations with a UNION between each KPI. In some areas of the script, the results are returned. But I find with the division calculations that this error crops up and it's puzzling. I will change my 1's to 1.00's and see how it works out. I will post whether if it was successful or not.

Dieter, you're right once again! The calculation does indeed work but now I show just integers for the calculations and no decimals! Any idea of how I can get the decimals?

When you SUM(1.00) the result should be a DECIMAL. So what do you mean by "just integers"?

You were talking about percentage, did you eventually multiply the result *** 100?**

Then simply change to **100 * SUM / SUM** instead of **SUM / SUM * 100** as Teradata rounds after each step in a calculation based on the requested fractional digits:

1.00 / 3.00 * 100 = (1.00 / 3.00) * 100 = 0.33 * 100 = 33.00 vs. 100 * 1.00 / 3.00 = (100 * 1.00) / 3.00 = 100.00 / 3.00 = 33.33

The basic rule is "multiply first, then divide"

Dieter

Hi Everyone,

I try to find my answer in the different threads regarding this error and I still have the 2616 error. I am doing a dashboard for different products and looking to compute percentages.

My code pulls from a CTE called Dashboard Report and the values range from 1,000 to 260,000 for the counts of sum products.