> >> 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).

>

Yeah, CASE is the way to go ;)

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com