CODEWITHSUNDEEP
sqlteradatadivide-by-zero
Jeremy
Jeremy

Reputation: 829

Teradata returning Division by Zero Error - How to Adress?

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

Upvotes: 0

Views: 9305

Answers (2)

dnoeth
dnoeth

Reputation: 60472

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

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133370

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
   ....

Upvotes: 1

Related Questions