This SELECT Failed 2616. ERROR is puzzling!

Database
Enthusiast

This SELECT Failed 2616. ERROR is puzzling!

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
Enthusiast

Re: This SELECT Failed 2616. ERROR is puzzling!

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

Enthusiast

Re: This SELECT Failed 2616. ERROR is puzzling!

RGLASS, THE DATATYPE OF THE COLUMN IS VARCHAR.

Enthusiast

Re: This SELECT Failed 2616. ERROR is puzzling!

No I mean the numeric column you are aggregating.

Enthusiast

Re: This SELECT Failed 2616. ERROR is puzzling!

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

Enthusiast

Re: This SELECT Failed 2616. ERROR is puzzling!

I would suggest you  check out

SQL Functions, Operators,

Expressions, and Predicates any release.

--SUM is valid only for numeric data.

 

Junior Contributor

Re: This SELECT Failed 2616. ERROR is puzzling!

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?

Enthusiast

Re: This SELECT Failed 2616. ERROR is puzzling!

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.

Enthusiast

Re: This SELECT Failed 2616. ERROR is puzzling!

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?

Junior Contributor

Re: This SELECT Failed 2616. ERROR is puzzling!

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"