1 million de thanks. Fidel

I already understood as the IIF works at Firebird.
=========
¦¦ ISMAEL ¦¦
=========
----- Mensaje original -----
De: Fidel Viegas
Para: firebird-support@yahoogroups.com
Enviado: viernes, 18 de abril de 2008 07:29
Asunto: Re: [firebird-support] WHERE and IIF


On Wed, Apr 16, 2008 at 10:50 PM, Ismael L. Donis García
<ismael@...> wrote:

> The function the IIF gives me error within the WHERE in the equal sign
> indicated in red. (Second equal sign after function IIF -->
> cofaccob.entidad='2578')
>
> select cofaccob.entidad, cofaccob.docemp, sum(cofaccob.haber) as haber from
> cofaccob where iif(cofaccob.clave='CS', cofaccob.entidad='2578',
> cofaccob.entidad='2580') group by cofaccob.entidad, cofaccob.docemp order by
> cofaccob.docemp

Hi Ismael,

Like Sean has said, you are using IIF incorrectly. IIF's syntax is the
following:

IIF (<search condition>, <value1>, <value2>), which is a short form for

case when <search condition> then <value1> else <value2>

In other words, it makes a test with the first expression, and if the
condition succeeds it returns value1, if it fails, then it returns
value2

What you are trying to do is incorrect in firebird. You are passing
three boolean expressions, two of which are invalid in Firebird. You
cannot pass boolean expressions for <value1> or <value2>.

You usually use iif to retrieve a result, and not to test a condition
in a where clause. This is how you would use it:

select iif(<search condition>, <value or expression 1>, <value or
expression 2>) [as alias], column1, column2 from table1 where column1
= <some value> or column2 = <some other value> etc...

The reason it works in MS Access may be because it accepts the two
boolean expressions in the IIF as valid. This is invalid in Firebird.
What you can do in Firebird is test it like this:

where iif(<search condition>, <value1>, <value2>) = <some value or expression>

Even though it does not make much sense, that will work, because iif
returns a value, and since you are testing a value with another value,
that works (ok, it works if the types match. That is, the type of the
result returned by iif has to be the same type of the value or result
of expression on the right hand side of the comparison)

I hope that clears your doubt with regards to iif's usage.

Now, if you want people to help you out with your question, you need
to clarify what you are trying to achieve, because it is not very
clear from the query you have provided. Taking a wild guess, I think
that what you are trying to achieve is this:

select cofaccob.entidad, cofaccob.docemp, sum(cofaccob.haber) as haber
from cofaccob
where cofaccob.clave='CS' and cofaccob.entidad in ('2578','2580')
group by cofaccob.entidad, cofaccob.docemp
order by cofaccob.docemp

not really sure if that is what you want, but just trying to guess as
you have not provided enough explanation for what you were trying to
achieve.

Fidel.




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