divide by zero error

[former_member229757]

Hi All,

while executing the query am getting an error divide by zero error.

Please provide solution.

select t0.DocEntry,t0.DocDate,t0.cardcode,t0.CardName, (T0.[GrosProfit]/(T0.[DocTotal] - T0.[VatSum])*100) As 'GrossProfit%',convert(int,t0.u_udf_quant) as Quant, t0.GrosProfit, (t0.DocTotal-t0.VatSum) as value from ORDR t0 where t0.DocDate between '06-01-2017' and '06-12-2017' and t0.CANCELED='N' order by (T0.[GrosProfit]/(T0.[DocTotal] - T0.[VatSum])*100) Desc

Thanks

Vinoth

[former_member185682 · Accepted solution]

Hi Vinoth,

This occured because you receive an error on T0.[DocTotal] - T0.[VatSum].

To avoid this you can use the function NULLIF. A sample below:

SELECT 
	t0.DocEntry,
	t0.DocDate,
	t0.cardcode,
	t0.CardName, 
	(T0.[GrosProfit]/ NULLIF((T0.[DocTotal] - T0.[VatSum]), 0) * 100) As 'GrossProfit%',
	convert(int,t0.u_udf_quant) as Quant, 
	t0.GrosProfit, (t0.DocTotal-t0.VatSum) as value 
FROM 
	ORDR t0 
WHERE 
	t0.DocDate BETWEEN '06-01-2017' and '06-12-2017' AND t0.CANCELED='N' 
ORDER BY 
	(T0.[GrosProfit] / NULLIF((T0.[DocTotal] - T0.[VatSum]), 0) * 100) DESC

When the result of T0.[DocTotal] - T0.[VatSum] is "0" the sql will replace by NULL. Then T0.[GrosProfit] / NULL * 100 = NULL. No error will be fired.

Hope it helps.

Kind Regards,

Diego Lother

[Johan_H · Accepted solution]

N.B.

NULLIF assumes a HANA database. If the database is MS SQL, use ISNULL((T0.[DocTotal]- T0.[VatSum]),0)

[former_member185682 · Accepted solution]

Hi Johan,

NULLIF is a function in SQL Server too, your purpose is the opposite of ISNULL. NULLIF sets a null for pre-defined value. ISNULL sets a pre-defined value for a null value.

Kind Regards,

Diego Lother

[Johan_H · Accepted solution]

Thanks Diego, you learn something new every day 🙂

Since what MS SQL version can I use NULLIF?

Regards,

Johan

[former_member185682 · Accepted solution]

This function was added since 2008 version. I think very usefull, it's better than use case when clause when you need convert 0 to null.

The reference: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql

Kind Regards,

Diego Lother

[former_member229757]

Hi Diego,

Thanks its working fine.