You can use CASE just fine:

SELECT m.Id, tr.Text,
sum(m.Valor) as Valor,
CASE When oi.Area = 0 Then
0
ELSE
sum(m.Valor / oi.Area)
END as ValorM2,
CASE When oi.Saldo = 0 Then
0
ELSE
sum(m.Valor / oi.Saldo) * 100
END 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

CASE can ALWAYS be used in place of IIF.

Jason

Luis Madaleno wrote:

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