Re: Division by zero

On Wed, Jun 3, 2009 at 9:18 PM, Oliver Kohll - Mailing Lists <
oliver(dot)lists(at)gtwm(dot)co(dot)uk> wrote:

> 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.
>
>
Putting that in a function is definitely going to be expensive..

You need to take care of only one case here: denominator == 0; rest of the
cases will be handled sanely by the database.

CASE WHEN packet_size = 0 THEN null ELSE cost/packet_size END as unit_cost

Best regards,

--
Lets call it Postgres

EnterpriseDB http://www.enterprisedb.com

gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device