No, I don't think this is doable. Like EXISTS, I expect COALESCE to be
executed once for every potential row, and then there is no way an
index can be used.

Create a stored procedure or use two separate queries in your program.
I'd be positively surprised if someone found any other solution that
made it possible for you to use an index without making it slower than
NATURAL.

Sorry,
Set

--- In firebird-support@yahoogroups.com, Radu Sky wrote:
> Hello,
>
> I have some queries with this type of WHERE:
> WHERE FK_KEY=COALESCE(:param,FK_KEY)
>
> I read this type of WHERE in some posts before and it is working as I
> expected on the results side, however it doesn't use indexes.
> Is there a way to do this with index (get all records when :param is
> null and the indexed ones when :param is set to some value)?
>
>
> TIA
> Radu