Teradata returning Division by Zero Error - How to Adress?

Question

The following query utilizes a case statement with some division. However, it is getting snagged on a division by zero error. Any ideas on how to handle the error exception would be greatly appreciated!

CASE 
    WHEN Channel = 'DA' THEN  CAST (CASE_QTY AS DECIMAL(38,0))/SUM(CAST(CASE_QTY AS DECIMAL(38,2))) OVER (PARTITION BY ld.LOAD_ID) 
    WHEN Channel = 'SS' THEN  CAST (CASE_QTY AS DECIMAL(38,0))/SUM(CAST(CASE_QTY AS DECIMAL(38,2))) OVER (PARTITION BY ld.LOAD_ID) 
    WHEN Channel = 'XDOCK' THEN  CAST (CASE_QTY AS DECIMAL(38,0))/SUM(CAST(CASE_QTY AS DECIMAL(38,2))) OVER (PARTITION BY ld.LOAD_ID)
    WHEN Channel = '?' THEN  CAST (CASE_QTY AS DECIMAL(38,0))/SUM(CAST(CASE_QTY AS DECIMAL(38,2))) OVER (PARTITION BY ld.LOAD_ID) 
    ELSE 0
    END AS Percentage

dnoeth · Accepted Answer · 2016-09-21T21:29:56.657

3

Simply use NULLIF. Additionally you can simplify your query to:

CASE 
    WHEN Channel IN ('DA','SS','XDOCK','?') 
      THEN Cast(CASE_QTY AS DECIMAL(38,0))/NullIf(Sum(Cast(CASE_QTY AS DECIMAL(38,2))) Over (PARTITION BY ld.LOAD_ID),0) 
    ELSE 0
END AS Percentage

edited Sep 21 '16 at 21:29

answered Sep 21 '16 at 18:10

dnoeth

59,503
4
39
56

This is giving me a syntax error "expected something between ')' and ')' – Jeremy Sep 21 '16 at 21:20
@Jeremy: Ops, forgot the `,0`, fixed. – dnoeth Sep 21 '16 at 21:30

score 1 · Answer 2 · answered Sep 21 '16 at 17:54

You can check with another case

  CASE  WHEN SUM(CAST(CASE_QTY AS DECIMAL(38,2)) <> 0 THEN
              WHEN Channel = 'DA' THEN  CAST (CASE_QTY AS DECIMAL(38,0))/SUM(CAST(CASE_QTY AS DECIMAL(38,2))) OVER (PARTITION BY ld.LOAD_ID)
        THEN -1 /*  or the value you prefer for zero value */
        END
   ....

Teradata returning Division by Zero Error - How to Adress?

2 Answers2