Divide by zero error encountered.

[Cpreston]

Hi

I have a Divide by zero error encountered. and have googled it and I have tried to use NULLIF in my syntax. However, I just cannot seem to get the syntax correct.

Could someone advise how the syntax should be please so I can avoid the Divide by zero error message, thanks in advance

SELECT     dbo.PurchaseOrderHeader.DateTimeCreated, dbo.PurchaseOrderHeader.DateExpected, dbo.PurchaseOrderLine.Quantity, dbo.PurchaseOrderLine.QuantityReceived, 
                      dbo.PurchaseOrderLine.Quantity - dbo.PurchaseOrderLine.QuantityReceived AS PoOutstandingQty, 
                      dbo.PurchaseOrderLine.UnitCostPrice * dbo.PurchaseOrderLine.Quantity + dbo.PurchaseOrderLine.TotalAdditionalCost AS PoOriginalValue, 
                      dbo.PurchaseOrderLine.TotalAdditionalCost / dbo.PurchaseOrderLine.TotalVolume AS AdditonalCost, dbo.PurchaseOrderLine.UnitCostPrice AS CostperM3, 
                      dbo.PurchaseOrderLine.TotalAdditionalCost / dbo.PurchaseOrderLine.TotalVolume + dbo.PurchaseOrderLine.UnitCostPrice AS CombineCostM, 
                      dbo.PurchaseOrderHeader.TimberMill, dbo.Supplier.Name, dbo.PurchaseOrderHeader.OrderStatus AS DetermineOUtstandingPO, 
                      dbo.PurchaseOrderLine.LineStatus AS IndetfyOrderComplete, dbo.Product.ProductCode, dbo.Product.Description, dbo.Branch.Name AS Quay, 
                      dbo.Users.Name AS POCreated, dbo.PurchaseOrderHeader.OrderType

 

[fredericofonseca]

use a case statement.
e.g.
case
when column2 = 0
then null -- or zero
else column1 / column2
end as resultname

And as mentioned many times before, put formatted code here and use alias to your tables - the way you have it makes it impossible to even understand what each field is

select poh.DateTimeCreated
     , poh.DateExpected
     , pol.Quantity
     , pol.QuantityReceived
     , pol.Quantity - pol.QuantityReceived as pooutstandingqty
     , pol.UnitCostPrice * pol.Quantity + pol.TotalAdditionalCost as pooriginalvalue
     , pol.TotalAdditionalCost / pol.TotalVolume as additonalcost
     , pol.UnitCostPrice as costperm3
     , pol.TotalAdditionalCost / pol.TotalVolume + pol.UnitCostPrice as combinecostm
     , poh.TimberMill
     , su.Name
     , poh.OrderStatus as determineoutstandingpo
     , pol.LineStatus as indetfyordercomplete
     , pr.ProductCode
     , pr.Description
     , br.Name as quay
     , us.Name as pocreated
     , poh.OrderType
from PurchaseOrderHeader poh
   , users us
   , product pr
   , branch br
   , PurchaseOrderLine pol
   , Supplier su

the above is easier to read than the code below

select dbo.PurchaseOrderHeader.DateTimeCreated
     , dbo.PurchaseOrderHeader.DateExpected
     , dbo.PurchaseOrderLine.Quantity
     , dbo.PurchaseOrderLine.QuantityReceived
     , dbo.PurchaseOrderLine.Quantity - dbo.PurchaseOrderLine.QuantityReceived as pooutstandingqty
     , dbo.PurchaseOrderLine.UnitCostPrice * dbo.PurchaseOrderLine.Quantity + dbo.PurchaseOrderLine.TotalAdditionalCost as pooriginalvalue
     , dbo.PurchaseOrderLine.TotalAdditionalCost / dbo.PurchaseOrderLine.TotalVolume as additonalcost
     , dbo.PurchaseOrderLine.UnitCostPrice as costperm3
     , dbo.PurchaseOrderLine.TotalAdditionalCost / dbo.PurchaseOrderLine.TotalVolume + dbo.PurchaseOrderLine.UnitCostPrice as combinecostm
     , dbo.PurchaseOrderHeader.TimberMill
     , dbo.Supplier.Name
     , dbo.PurchaseOrderHeader.OrderStatus as determineoutstandingpo
     , dbo.PurchaseOrderLine.LineStatus as indetfyordercomplete
     , dbo.Product.ProductCode
     , dbo.Product.Description
     , dbo.Branch.Name as quay
     , dbo.Users.Name as pocreated
     , dbo.PurchaseOrderHeader.OrderType
from dbo.PurchaseOrderHeader
   , dbo.users
   , dbo.product
   , dbo.branch
   , dbo.PurchaseOrderLine
   , dbo.Supplier

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Managed to sort as below

dbo.PurchaseOrderLine.UnitCostPrice * dbo.PurchaseOrderLine.Quantity + dbo.PurchaseOrderLine.TotalAdditionalCost AS PoOriginalValue,
'NULLIF(dbo.PurchaseOrderLine.TotalAdditionalCost / dbo.PurchaseOrderLine.TotalVolume)' AS AdditonalCost, dbo.PurchaseOrderLine.UnitCostPrice AS CostperM3,
'NULLIF(dbo.PurchaseOrderLine.TotalAdditionalCost / dbo.PurchaseOrderLine.TotalVolume + dbo.PurchaseOrderLine.UnitCostPrice)' AS CombineCostM3,
dbo.PurchaseOrderHeader.TimberMill, dbo.Supplier.Name, dbo.PurchaseOrderHeader.OrderStatus AS DetermineOUtstandingPO,

 

[gmmastros]

Divide by zero results in a error.
Divide by null results in null, so...

instead of this...

dbo.PurchaseOrderLine.TotalAdditionalCost / dbo.PurchaseOrderLine.TotalVolume AS AdditonalCost,

Use this....

dbo.PurchaseOrderLine.TotalAdditionalCost / [!]NullIf([/!]dbo.PurchaseOrderLine.TotalVolume[!], 0)[/!] AS AdditonalCost,

If you want to return Zero instead of null, then do this...

[!]Coalesce([/!]dbo.PurchaseOrderLine.TotalAdditionalCost / [!]NullIf([/!]dbo.PurchaseOrderLine.TotalVolume[!], 0), 0)[/!] AS AdditonalCost,

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom