Subject Re: [firebird-support] Optional parameter in a select statement
Author Ivan Prenosil
> I'm trying to do a select where I can get the log records from a
> specific User, or records of all user if '-1' is passed in the parameter.
> I'm using the following select statement:
> select * from LogTable
> where ( (:User = cast('-1' as char(15))) or (User = :User))
> It works but the plan I'm receiving from IBExperts is always NATURAL,

It is because this part of where clause
( (:User = cast('-1' as char(15)))
can't use index anyway, so using it for
(User = :User))
would just make things worse.

> how can I write the same code IN ONE STATEMENT (without an IF) and use
> the correct index?

select * from LogTable
where (User = :User)
select FIRST ( CASE WHEN :User='-1' THEN 1000000000 ELSE 0 END ) * from LogTable