Re: Division by zero

On 4 Jun 2009, at 13:11, Sam Mason <sam(at)samason(dot)me(dot)uk> 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