CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduresisnull
Ren Cantu
Ren Cantu

Reputation: 67

Divide by zero error encountered error. Null value is eliminated by an aggregate or other SET operation

In this specific part of a SP I'm trying to create, I'm getting an error:

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Divide by zero error encountered. Additional error <2>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation., SqlState: 01003, NativeError: 8153

I believe this is because I'm trying to divide by a null or 0. I'm unsure how I can work around this. I tried to use ISNULL but I think I'm not doing it right.

select 
    id_date,
    id_company,
    id_kpi,
    sum(CASE WHEN id_kpi=50 THEN -actual_mes END) /
    sum(CASE WHEN id_kpi=51 THEN actual_mes END) Amount
from dual;

Can someone point me in the right direction? I've looked at a few other threads on this, but I'm still not sure how to solve this.

Upvotes: 1

Views: 12329

Answers (3)

svenGUTT
svenGUTT

Reputation: 419

I put together a safe-divide function as we do a lot of dividing with our invoice data, and it saves from having to add conditionalized fluff when building our queries. Instead, just call the function with your enumerator and denominator and it will return 0 if your denominator is either 0 or NULL. You can adjust the @Return to a different data type if you wish (i.e. FLOAT) so it's a bit more apples to apples with the input...we just usually only need it to 4 decimals so we normalized it to that instead.

IF OBJECT_ID('dbo.fn_SafeDivide') IS NOT NULL
    DROP FUNCTION dbo.fn_SafeDivide
GO    

CREATE FUNCTION dbo.fn_SafeDivide(
@Numerator FLOAT, 
@Denominator FLOAT 
) 
RETURNS DEC(14,4) 
AS
BEGIN
DECLARE @Return DEC(14,4)

IF COALESCE(@Denominator, 0) = 0
    SET @Return = 0
ELSE
    SET @Return = @Numerator / @Denominator

RETURN @Return
END
GO

To Use:

select 
    dbo.fn_SafeDivide(
        SUM(CASE WHEN id_kpi=50 THEN -actual_mes END), 
        SUM(CASE WHEN id_kpi=51 THEN actual_mes END)
    ) as Amount
from dual;

Upvotes: 0

Vahid Iranshad
Vahid Iranshad

Reputation: 41

You can use case when .. else ... end and isnull e.g.

select 
    id_date,
    id_company,
    id_kpi,
    CASE WHEN ISNULL(sum(CASE  WHEN id_kpi=51 THEN ISNULL(actual_mes, 0) ELSE 0 END), 0) = 0
    THEN 0
    ELSE 
        sum(CASE  WHEN id_kpi=50 THEN -ISNULL(actual_mes, 0) ELSE 0 END) /
        sum(CASE  WHEN id_kpi=51 THEN ISNULL(actual_mes, 0) ELSE 0 END)
    END Amount
from dual;

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81970

Rather than the CASE, I usually opt for NullIf() on the denominator

Example

select id_date,
       id_company,
       id_kpi,
       sum(CASE  WHEN id_kpi=50 THEN -actual_mes END) /
       nullif(sum(CASE  WHEN id_kpi=51 THEN actual_mes END),0) Amount
  from dual;

Upvotes: 3

Related Questions