locked
Divide by zero error RRS feed

  • 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

    Tuesday, 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 )

    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/

     


    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)

     

     

    Tuesday, December 9, 2008 2:54 PM
    Answerer
  •  

    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

    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'

    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 )

    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/

     


    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)

     

     

    Tuesday, December 9, 2008 2:54 PM
    Answerer
  • Can you put that in the query and give me the entire code.. i am having hard time.. writing it .. getting error messages

     

    Tuesday, December 9, 2008 3:05 PM
  • If the extended_price is zero then i`ll want the result as 0.00

     

    Tuesday, December 9, 2008 3:07 PM
  • This

    Code Snippet
    (extended_price -  commission_cost) /( extended_price )

     

     

    should then become

     

    Code Snippet

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

     

     

    Tuesday, December 9, 2008 3:10 PM
    Answerer
  •  

    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

    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'

    Tuesday, December 9, 2008 4:10 PM
  • Hi Mangal,

     

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

     

    Tuesday, December 9, 2008 4:19 PM