Division by zero

Hello,

We have a system that allows users to create views containing
calculations but divisions by zero are commonly a problem.

An simple example calculation in SQL would be

SELECT cost / pack_size AS unit_cost from products;

Either variable could be null or zero.

I don't think there's a way of returning null or infinity for
divisions by zero, rather than causing an error but I'd just like to
check - and put in a vote for that functionality!

If not, I will have to get the system to check for any numeric fields
in user input calculations and rewrite them similar to

CASE WHEN cost IS NULL THEN null
WHEN pack_size IS NULL THEN null
WHEN cost = 0 THEN null
WHEN pack_size = 0 THEN null
ELSE cost / pack_size
AS unit_cost

I don't want to write new functions, I'd rather keep it in plain SQL.

Best regards

Oliver Kohll


oliver@gtwm.co.uk / 0845 456 1810 / 07814 828608
www.gtwm.co.uk - company
www.gtportalbase.com - product