divided by zero error

Question

Post reply

divided by zero error

kwoznica

SSCertifiable

Points: 5275
More actions
July 16, 2011 at 4:42 pm

#241653

Hey everyone! Hoping you can help me with a sql query problem I am having. I am creating a query against one of our Invoice tables but keep getting a divided by zero error message.
I have attached a sample in xlsx format of results from the Sales Invoice Line table. You will see the second line contains a 0 value in the Quantity column for the same sales invoice number. I would like to omit that record when I am running the below statement as I beleive it is what is cause the divided by zero error message.
I have also tried using a sub query to elmininate the 0 quantity record but get a different error message.
Also below is the sql statement I am running. Please let me know your thougts as it would be greatly appreciated.
select
sih.[No_] AS 'Invoice Number'
,sih.[Document Date] AS 'Invoice Date'
,sih.[Sell-to Customer No_] AS 'Bill-To Customer'
,sih.[Ship-to Name] AS 'Name'
,sih.[Payment Terms Code] AS 'Payment Terms'
,sih.[Order No_] AS 'Order No.'
,sih.[Salesperson Code] AS 'Person'
,((sil.[Quantity])*(sil.[Unit Price])) AS Amount
,((sil.[Quantity]*sil.[Unit Price])-(sil.[Quantity]*sil.[Unit Cost (LCY)])) AS Profit
,(Select
(
(
(
(sil.[Quantity] * sil.[Unit Price])-(sil.[Quantity] * sil.[Unit Cost (LCY)])
)
/(sil.[Quantity] * sil.[Unit Price])
)
* 100
) AS 'Profit %'
From [dbo].[GK Live$Sales Invoice Line]
Where Quantity > 0
)
From [dbo].[GK Live$Sales Invoice Header] AS sih
Inner Join [dbo].[GK Live$Sales Invoice Line] AS sil ON sih.No_ = sil.[Document No_]
GROUP BY
sih.No_
,sih.[Document Date]
,sih.[Sell-to Customer No_]
,sih.[Ship-to Name]
,sih.[Payment Terms Code]
,sih.[Order No_]
,sih.[Salesperson Code]
,sil.Quantity
,sil.[Unit Price]
,sil.[Unit Cost (LCY)]
Having sih.[Document Date] = '07/15/2011'
any help or thoughts are appreciated.
Thanks
Keith

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could use

/(NULLIF(sil.[Quantity],0))

This would cause a divide by NULL which will return NULL.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

kwoznica SSCertifiable Points: 5275 More actions · Answer 2

Thanks LutzM.

Here is what I ended up with and it seems to be working. I really appreciate that quick response.

select

sih.[No_] AS 'Invoice Number'

,sih.[Document Date] AS 'Invoice Date'

,sih.[Sell-to Customer No_] AS 'Bill-To Customer'

,sih.[Ship-to Name] AS 'Name'

,sih.[Payment Terms Code] AS 'Payment Terms'

,sih.[Order No_] AS 'Order No.'

,sih.[Salesperson Code] AS 'Person'

,((sil.[Quantity])*(sil.[Unit Price])) AS Amount

,((sil.[Quantity]*sil.[Unit Price])-(sil.[Quantity]*sil.[Unit Cost (LCY)])) AS Profit

,(

(

(sil.[Quantity] * sil.[Unit Price])-(sil.[Quantity] * sil.[Unit Cost (LCY)])

)

/(NULLIF(sil.[Quantity],0.0) * (NullIF(sil.[Unit Price],0.0))

)

* 100

)) AS 'Profit%'

From [dbo].[GK Live$Sales Invoice Header] AS sih

Inner Join [dbo].[GK Live$Sales Invoice Line] AS sil ON sih.No_ = sil.[Document No_]

GROUP BY

sih.No_

,sih.[Document Date]

,sih.[Sell-to Customer No_]

,sih.[Ship-to Name]

,sih.[Payment Terms Code]

,sih.[Order No_]

,sih.[Salesperson Code]

,sil.Quantity

,sil.[Unit Price]

,sil.[Unit Cost (LCY)]

Having sih.[Document Date] = '07/15/2011'

LutzM SSC Guru Points: 107049 More actions · Answer 3

Glad I could help 😀

And thank you for the feedback!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]