On Wed, Jun 3, 2009 at 9:18 PM, Oliver Kohll - Mailing Lists < oliver.li...@gtwm.co.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[.sin...@enterprisedb.com singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com Mail sent from my BlackLaptop device
