Answered by:
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 #temp2
drop table #temp3select customer_id,inv_mast_uidINTO #tempfrom p21_view_oe_line l left join p21_view_oe_hdr h onh.oe_hdr_uid=l.oe_hdr_uidwhere datediff(dd,l.date_created,getdate()) < 7 andh.location_id=108843select h.customer_id,l.inv_mast_uid,max(oe_line_uid) oe_line_uidINTO #temp2from p21_view_oe_line lleft join p21_view_oe_hdr h on h.oe_hdr_uid=l.oe_hdr_uidleft join #temp t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uidwhere datediff(dd,l.date_created,getdate()) <= 1 andh.location_id=108843group 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_margininto #temp3FROM oe_hdr h left join p21_view_oe_line l on l.oe_hdr_uid=h.oe_hdr_uidleft join #temp2 t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uidand 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 0else h.order_date end as 'Previous Order Date'
from p21_view_oe_line lleft join p21_view_oe_hdr h on l.oe_hdr_uid=h.oe_hdr_uidleft join #temp3 t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uidand datediff(dd,l.date_created,getdate()) <= 7left join p21_view_inv_mast m on m.inv_mast_uid=t.inv_mast_uidTuesday, December 9, 2008 2:48 PM
Answers
-
Most probably extended_price is having 0 somewhere.
Wherever you are having /extended_price do
/ NULLIF (extended_price,0 )
- Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:21 PM
Tuesday, December 9, 2008 2:52 PM -
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/
- Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:21 PM
- Edited by Kalman Toth Sunday, January 29, 2012 6:04 AM
Tuesday, December 9, 2008 2:53 PM -
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)- Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:21 PM
Tuesday, December 9, 2008 2:54 PMAnswerer -
SELECT
h.customer_id,l.inv_mast_uid,coalesce
((l.extended_price - l.commission_cost) / NullIf(l.extended_price,0 ),0 )as prev_margininto
#temp3FROM
oe_hdr h left join p21_view_oe_line l on l.oe_hdr_uid=h.oe_hdr_uidleft
join #temp2 t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uidand
t.oe_line_uid = l.oe_line_uidselect
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 lleft
join p21_view_oe_hdr h on l.oe_hdr_uid=h.oe_hdr_uidleft
join #temp3 t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uidand
datediff(dd,l.date_created,getdate()) <= 7left
join p21_view_inv_mast m on m.inv_mast_uid=t.inv_mast_uid- Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:22 PM
Tuesday, December 9, 2008 3:39 PM -
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'
- Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:22 PM
Tuesday, December 9, 2008 4:10 PM
All replies
-
Most probably extended_price is having 0 somewhere.
Wherever you are having /extended_price do
/ NULLIF (extended_price,0 )
- Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:21 PM
Tuesday, December 9, 2008 2:52 PM -
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/
- Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:21 PM
- Edited by Kalman Toth Sunday, January 29, 2012 6:04 AM
Tuesday, December 9, 2008 2:53 PM -
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)- Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:21 PM
Tuesday, December 9, 2008 2:54 PMAnswerer -
Can you put that in the query and give me the entire code.. i am having hard time.. writing it .. getting error messagesTuesday, December 9, 2008 3:05 PM
-
If the extended_price is zero then i`ll want the result as 0.00Tuesday, December 9, 2008 3:07 PM
-
This
Code Snippet(extended_price - commission_cost) /( extended_price )should then become
Code SnippetCoalesce((extended_price - commission_cost) / (NullIf(extended_price, 0)), 0)
Tuesday, December 9, 2008 3:10 PMAnswerer -
SELECT
h.customer_id,l.inv_mast_uid,coalesce
((l.extended_price - l.commission_cost) / NullIf(l.extended_price,0 ),0 )as prev_margininto
#temp3FROM
oe_hdr h left join p21_view_oe_line l on l.oe_hdr_uid=h.oe_hdr_uidleft
join #temp2 t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uidand
t.oe_line_uid = l.oe_line_uidselect
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 lleft
join p21_view_oe_hdr h on l.oe_hdr_uid=h.oe_hdr_uidleft
join #temp3 t on t.customer_id = h.customer_id and t.inv_mast_uid = l.inv_mast_uidand
datediff(dd,l.date_created,getdate()) <= 7left
join p21_view_inv_mast m on m.inv_mast_uid=t.inv_mast_uid- Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:22 PM
Tuesday, December 9, 2008 3:39 PM -
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 ?
Tuesday, December 9, 2008 4:00 PM -
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'
- Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:22 PM
Tuesday, December 9, 2008 4:10 PM -
Hi Mangal,
Thank you for that.. tried it.. it works..
Tuesday, December 9, 2008 4:19 PM