Skip to Content

divide by zero error

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jul 17, 2017 at 03:05 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 18, 2017 at 10:10 AM

    Hi Diego,

    Thanks its working fine.

    Add comment
    10|10000 characters needed characters exceeded