Division by zero Error

Database
Enthusiast

Division by zero Error

I'm trying to execute a query which fails on divide by zero error.

Can't use a NULLIFZERO because the AJI would be ignored.

Following is the query which fails on "SELECT Failed. 2618: Invalid Calculation: Division by zero"

SELECT DISTINCT C0, C1, AVG(C2) OVER (PARTITION BY C0,C1) AS C5

FROM

(SELECT Equipment_Category.Category_Code    C0 ,

    Equipment_Class.Cat_Class_Description     C1 ,

    SUM(Equipment_Daily_Metrics.c10)     C2  

 FROM

 (

  (CG_EQPT_CATEGORY Equipment_Category

  INNER JOIN

  (SELECT SUBSTRING ('000' || TRIM(BOTH FROM Category_Code) FROM 1 + CHAR_LENGTH (TRIM(BOTH FROM Category_Code))) || '-' ||

      SUBSTRING ('0000' || TRIM(BOTH FROM Class_Code) FROM 1 + CHAR_LENGTH (TRIM(BOTH FROM Class_Code))) || ' ' ||

      CG.Class_Name AS Cat_Class_Description,

      CG.Category_Code Category_Code,

      CG.Class_Code Class_Code

   FROM CG_ITEM_EQPT_CLASS CG

  ) Equipment_Class

  ON Equipment_Category.Category_Code = Equipment_Class.Category_Code

  )

  INNER JOIN

  (SELECT Equipment_Cost_Amt C10 ,Category_Code, Class_Code

   FROM CG_EQUIPMENT_DAILY_FACT) Equipment_Daily_Metrics

  ON Equipment_Class.Category_Code = Equipment_Daily_Metrics.Category_Code

  AND Equipment_Class.Class_Code = Equipment_Daily_Metrics.Class_Code

  )

 GROUP BY C0, C1

) T0

WHERE T0.C0 BETWEEN 100 AND 200

Couple of other scenarios...

I included the following clause in the query and it didn't error out.

WHERE T0.C2 <> 0

AND   T0.C0 BETWEEN 100 AND 200

However when i attempt to see which are offending recods by querying the following, there are none.

WHERE T0.C2 = 0

AND   T0.C0 BETWEEN 100 AND 200

Also, I can create a table over this query and can successfully query from it

SELECT DISTINCT C0,C1, AVG(C2) OVER (PARTITION BY C0,C1) C5 FROM SSURI.TEST1;

Why would the query fail on division by zero error if there aren't any T2.C2 = 0 records.

6 REPLIES
Junior Contributor

Re: Division by zero Error

Is this the actual query? There's no division at all.

Is it based on a view with a division in it?

Otherwise you might get a "Division by Zero" even when there's no single row with a zero in it if you sum it up and it happens to sum to a zero.

Dieter

Enthusiast

Re: Division by zero Error

It's failing on the AVG() function.

SELECT DISTINCT C0, C1, AVG(C2) OVER (PARTITION BY C0,C1) AS C5

Junior Contributor

Re: Division by zero Error

When you're 100% shure it's failing due to the AVG you should open an incident.

AVG(col) is SUM(col)/COUNT(col), a divivion by zero is only possible with be a zero as the result of a COUNT when there are only NULLs. But then it's NULL/0 which should return NULL.

Dieter

Enthusiast

Re: Division by zero Error

Well, i can do the following without errors

1> SELECT * from (Query)

2> SELECT C0,C1, C2 from (Query)

But, when i try to do a SELECT C0, C1, AVG(C2) OVER (PARTITION BY C0,C1), it fails with Division by zero.

I might be wrong, but from the look of it, it tends to imply that for some reason AVG is causing the error.

May be i should open a ticket with GSC.

Sanjeev

Teradata Employee

Re: Division by zero Error

Try:

SELECT AVG(C2) .... if it fails .... open an incident .... if it doesnt .... its not coz of AVG.

Re: Division by zero Error

I had the same probelm, avg was giving me an error because all the values in a particular category were null. 

I tested by removing null values before running the querry and it worked fine.

I fixed it by using the vollowing code:

select

NULLIFZERO(SUM(c2) OVER(PARTITION BY c1, c0)) AS tmp1,

NULLIFZERO(COUNT(c2) OVER(PARTITION BY c1, c0)) AS tmp2,

tmp1/tmp2 AS new_value