Re: Division by zero

[Oliver Kohll - Mailing Lists <oliver.lists@xxxxxxxxxx>]

On 4 Jun 2009, at 13:11, Sam Mason <sam@xxxxxxxxxxxxx> wrote:

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

Yes; or even shorter:

 cost/nullif(packet_size,0) AS unit_cost

Thanks Sam and others. nullif is a good one to remember. However my problem is I want to be able to deal with an arbitrary calculation a user may create. In the light of a new day I realise this is obviously not trivial and would entail reasonably complex parsing. You'd have to find elements that could cause an error (division, some aggregates) and insert in the correct place nullif or CASE, taking care of bracket matching for starters - a messy workaround to the problem.

I might look into functions if that's the only reasonable way of catching exceptions.

Oliver