Re: division by zero error in a request

Bernard Grosperrin wrote:
> I wants to make a view giving me some statistics.
>
> I am not sure to understand why something like this
>
> SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) /
> (sold_parts_amount_dly + sold_labor_amount_dly) from sales
>
> give me a division by zero error?
>
> If that is not the way to go, should I write a function that I would call
> instead?
>
> Thanks,
> Bernard

Hi Bernard,

In mathematics, you cannot divide by zero. So 4/0 is not possible for
example. In your SELECT query, sold_parts_amount_dly and
sold_labor_amunt_dly are zero in some cases, giving you the division by
zero error.

You could solve this by using CASE.
http://www.postgresql.org/docs/8.1/static/functions-conditional.html

Or you could create a pl/pgsql function that 1.) either uses exceptions
to handle the division by zero error or 2.) check that
sold_parts_amount_dly and sold_labor_amount_dly are not zero before
dividing by them within a function.

The simplest would be to use CASE in your query.

regards,
karen