Olaf Kluge skriver:

> Hello,
>
> we are using firebird 2.5 classic server 64 bit on windows server 2008
> standard 64 bit.
>
> My first question. Is the last line possible? (iif(select condition =
> true,'where field = value','where field = value2)
>
> select first (1) a.fachnr, a.le
> from tfachzuordnung a
> where a.dispo = 0 and a.gesperrt = 0 and a.elementtyp = 1 and
> a.paltyp = :paltyp
> and a.le is not null
> and (select count (*) from tlepos where bestandsnr is not null and
> leid = a.le) = 4
> and iif((select count(*) from tfachres where artnr = :artnr) >0,
> 'a.dispo = 0', 'bestandsnr is null')

Not sure if FB has any iif construct (an UDF?) but you can refactor as
follows (untested!):

...
and (
(select count(*) from tfachres where artnr = :artnr) > 0
and a.dispo = 0
or coalesce((select count(*) from tfachres where artnr = :artnr), 0) <= 0
and bestandsnr is null
)

Not sure if the query optimizer is smart enough to eval the count
subquery only once.

Kjell
--
------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64


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