Catching Divide by Zeros

[BobLoblaws]

Let's say this is my query.

SELECT InvoiceNumber,
InvoiceDate,
Product,
(QtyReturned / QtyOrdered) AS '% Returned'
FROM Invoices
...

Occasionally the QtyOrdered may be 0 causing a Divide By Zero error. How can I catch it, and instead of QtyReturned / 0, make it QtyReturned / 1?

Thanks.

 

[bperry]

This should do it:

SELECT InvoiceNumber,
InvoiceDate,
Product,
(QtyReturned /
(CASE QtyOrdered when 0 then 1 else QtyOrdered END)) AS '% Returned'
FROM Invoices


rgrds, etc
bp

 

[arrowhouse]

Only down side of doing this is you can have quanities that look like valid percentages. (eg in the above 99 returned with 0 orders looks like 99%)

Maybe this is not an issue for you, as presumably you don't get returns on things that havn't been ordered...or may be you do. Aussumptions can be so dangerous.

I prefer to set to 0 (or 100, or some other value that is appropriate) dependant on the sort of % result I'm looking at:

SELECT InvoiceNumber,
InvoiceDate,
Product,
CASE WHEN QtyOrdered = 0 THEN 0 --or 100 whatever
ELSE (QtyReturned / QtyOrdered )
END AS '% Returned'
FROM Invoices


If you can deal with non numeric results then you could also output some sort of warning statement instead.


Case is defintely the way to go.

 

[BobLoblaws]

Thanks for the help.