Martijn Tonies wrote:

> Hi,
>
>> I have a query that looks something like:
>> SELECT
>> S.PRICE SALEPRICE,
>> E.PRICE ENTRYPRICE,
>> (S.PRICE -I.PRICE)/I.PRICE PERCENT
>> FROM SALES S, ENTRYS E
>> WHERE S.IDENTRY = E.ID
>>
>> The problem is that I.PRICE can be 0 and i get a division by 0 error. Is
>> there a way to make PERCENT = 100 if I.PRICE = 0 without running two
> querys?
>
> You could try with COALESCE. Check the Firebird 1.5 release notes
> for the syntax.

I thought exactly as you did, Martijn, but we're both wrong! COALESCE takes the first value that is not NULL, and 0 is a value. So Alan's suggestion is the way to go (well, except that I expect that 1 and not 100 is 100% when looking at the formulae and that 'I' isn't a table alias mentioned in the FROM/JOIN clauses).

Set


[Non-text portions of this message have been removed]