Skip to Content
0

divide by zero error

Jul 17, 2017 at 02:49 PM

68

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
DIEGO LOTHER Jul 17, 2017 at 03:05 PM
1

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

Show 4 Share
10 |10000 characters needed characters left characters exceeded

N.B.

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

0

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

1

Thanks Diego, you learn something new every day :-)

Since what MS SQL version can I use NULLIF?

Regards,

Johan

0

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

1
Vinoth D R Jul 18, 2017 at 10:10 AM
0

Hi Diego,

Thanks its working fine.

Share
10 |10000 characters needed characters left characters exceeded