Hi,

What I want to do here is just trying to avoid a division by zero error
when oi.area is zero.
my original query works fine:

SELECT m.Id, tr.Text,
sum(m.Valor) as Valor,
sum(m.Valor / oi.Area) as ValorM2,
sum(m.Valor / oi.Saldo) * 100 as PerTotal
FROM table1 M
LEFT JOIN table2 TR ON (tr.ID = m.ID)
LEFT JOIN table3 OI ON (oi.ID = m.ID)
WHERE (M.ID=1)
GROUP BY m.id, tr.text
ORDER BY m.id

but when oi.area or oi.saldo is zero the query gives an error.
since there is no IIF() function, the only solution is to use a CASE
but I guess I wont be able to do this.

It is a good thing for FB2 to have or an IIF() function or this kind of
CASE use possible.

Regards and thanks,

Luis



Ann W. Harrison wrote:

>Hans wrote:
>
>
>>Usually you have to add the fields in the SUM( ) calculations to the GROUP
>>BY
>>statement.
>>
>>
>
>No, SUM is an aggregate and that field doesn't have to be in the GROUP
>BY. The problem field is here:
>
> > CASE oi.area
>
>oi.area is not in the GROUP BY and CASE is not an aggregate.
>
>
>Regards,
>
>
>Ann
>
>
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>