Database

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-20-2014
08:12 AM

08-20-2014
08:12 AM

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.

SELECT

'7. PRODUCT_DEPLETION %'

,CAST (SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'C001' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST(SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'CAPE' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0001"

, CAST (SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'C002' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST (SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'CACO' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0002"

, CAST (SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'C003' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST(SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'CADI' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0003"

, CAST (SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'C004' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST(SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'C005' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0004"

, CAST (SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'CACM' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST(SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'C006' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0005"

, CAST (SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'CAIN' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST(SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'C007' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0006"

, CAST (SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'CAIT' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST(SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'C008' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0007"

, CAST (SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'CAPB' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST(SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'C009' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0008"

, CAST(SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'CAWM' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST(SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'C010' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0009”

, CAST(SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'USBA' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST(SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'C011' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0010"

, CAST(SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'USDE' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST(SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'C012' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0011"

, CAST(SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND SRC_ORG_UNT_CD = 'USWI' AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL (10,2))/

NULLIFZERO(CAST(SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) AND SRC_ORG_UNT_CD = 'C013' THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TRA-0012"

, CAST (SUM(CASE WHEN PRODUCT_NM IN('HOUSEWARES','CLOTHING') AND WKFLW_STS_NM IN (‘RELEASED’, ‘HELD’) THEN 1 ELSE 0 END) AS DECIMAL(10,2))/

NULLIFZERO(CAST( SUM(CASE WHEN PRODUCT_NM IN(‘INVENTORY’) THEN 1 ELSE 0 END) AS DECIMAL(10,2))) AS "TOTAL"

FROM

DASHBOARD_REPORT

10 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-20-2014
08:46 AM

08-20-2014
08:46 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-20-2014
09:00 AM

08-20-2014
09:00 AM

RGLASS, THE DATATYPE OF THE COLUMN IS VARCHAR.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-20-2014
10:53 AM

08-20-2014
10:53 AM

No I mean the numeric column you are aggregating.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-20-2014
11:36 AM

08-20-2014
11:36 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-20-2014
12:14 PM

08-20-2014
12:14 PM

I would suggest you check out

**SQL Functions, Operators,**

**Expressions, and Predicates any release.**

**--SUM is valid only for numeric data.**

** **

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-20-2014
01:53 PM

08-20-2014
01:53 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-20-2014
09:13 PM

08-20-2014
09:13 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-21-2014
07:57 AM

08-21-2014
07:57 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-21-2014
08:21 AM

08-21-2014
08:21 AM

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"

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.