Hi,

No I can't. It always returns the same error:
-104 Invalid expression in the select list (not contained in either an
aggregate function or the GROUP BY clause).

Luis

Jason Dodson wrote:

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