Divide by zero error

Question

When i run this query i get an error as divide by zero encountered.. can you tell me what am i missing ?

 

 

 

drop table #temp
drop table #temp2
drop table #temp3

select customer_id,inv_mast_uid

INTO #temp

from p21_view_oe_line l left join p21_view_oe_hdr h on

h.oe_hdr_uid=l.oe_hdr_uid

where datediff(dd,l.date_created,getdate()) < 7 and

h.location_id=108843

select h.customer_id,l.inv_mast_uid,max(oe_line_uid) oe_line_uid

INTO #temp2

from p21_view_oe_line l

left join p21_view_oe_hdr h on h.oe_hdr_uid=l.oe_hdr_uid

left join #temp t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uid

where datediff(dd,l.date_created,getdate()) <= 1 and

h.location_id=108843

group by h.customer_id,l.inv_mast_uid

--drop table #temp3

SELECT h.customer_id,l.inv_mast_uid,(l.extended_price -  l.commission_cost) / (l.extended_price ) as prev_margin

into #temp3

FROM oe_hdr h left join p21_view_oe_line l on l.oe_hdr_uid=h.oe_hdr_uid

left join #temp2 t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uid                 

and t.oe_line_uid = l.oe_line_uid
 
 
select h.customer_id, h.order_no,m.item_id,h.order_date,(extended_price -  commission_cost) /( extended_price ) as curr_margin,

t.prev_margin,

case when t.prev_margin = 0 then 0

            else  h.order_date  end as 'Previous Order Date'

from p21_view_oe_line l

left join p21_view_oe_hdr h on l.oe_hdr_uid=h.oe_hdr_uid

left join #temp3 t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uid

and datediff(dd,l.date_created,getdate()) <= 7

left join p21_view_inv_mast m on m.inv_mast_uid=t.inv_mast_uid

Answer 1

 

Most probably extended_price is having 0 somewhere.

Wherever you are having /extended_price  do 

 / NULLIF (extended_price,0 )

Answer 2

You have to check the values in each division to make sure they are not zero. For example, extended_price.

Check the column the zero values.

You may use CASE conditional expression for defaulting it to a value:

http://www.sqlusa.com/bestpractices/training/scripts/casefunction/

 

Answer 3

What do you want to do when extended_price is zero?

 

If you want to change it's value to 1, effectively returning the values without division (x / 1 = x) then

 

Code Snippet

... / Coalesce(NullIf(extended_price, 0), 1)

 

 

If you want to return NULL (methematically this is normally the desired method) then simply drop the coalesce

 

Code Snippet

... / NullIf(extended_price, 0)

 

 

Answer 4

Can you put that in the query and give me the entire code.. i am having hard time.. writing it .. getting error messages

 

Answer 5

If the extended_price is zero then i`ll want the result as 0.00

 

Answer 6

This

Code Snippet

(extended_price -  commission_cost) /( extended_price )

 

 

should then become

 

Code Snippet

Coalesce((extended_price -  commission_cost) / (NullIf(extended_price, 0)), 0)

 

 

Answer 7

 

SELECT h.customer_id,l.inv_mast_uid,

coalesce((l.extended_price - l.commission_cost) / NullIf(l.extended_price,0 ),0 )as prev_margin

into #temp3

FROM oe_hdr h left join p21_view_oe_line l on l.oe_hdr_uid=h.oe_hdr_uid

left join #temp2 t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uid

and t.oe_line_uid = l.oe_line_uid

 

 

 

select h.customer_id, h.order_no,m.item_id,h.order_date,Coalesce((extended_price - commission_cost) /NullIf( extended_price,0 ),0) as curr_margin,

t.prev_margin,

case when t.prev_margin = 0 then 0

else h.order_date end as 'Previous Order Date'

from p21_view_oe_line l

left join p21_view_oe_hdr h on l.oe_hdr_uid=h.oe_hdr_uid

left join #temp3 t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uid

and datediff(dd,l.date_created,getdate()) <= 7

left join p21_view_inv_mast m on m.inv_mast_uid=t.inv_mast_uid

Answer 8

 

Hello Mangal,

 

Can you also help me with the logic. I am using this case

 

case when t.prev_margin = 0 then 0

else h.order_date end as 'Previous Order Date'

when t.prev_margin col has nothing its 0 but if there`s a previous margin available then i want the date for that margin.

 

Is this logic that im using usefull ?

 

Answer 9

Well the logic is correct, nothing wrong with it. 

But question is when you said "when t.prev_margin col has nothing"  you mean prev_margin=0 or it is null?  if it is null it should be t.prev_margin IS NULL

And i guess instead of then 0 if you use THEN NULL that will be better or Should I say more appropriate.

Not necesarily but I guess this should be

case when t.prev_margin IS NULL  then NULL

else h.order_date end as 'Previous Order Date'

Answer 10

Hi Mangal,

 

Thank you for that.. tried it.. it works..