Subject RE: [firebird-support] Conditional/alternative where clauses
Author Svein Erling Tysvær
select <Field1, Field2, Filed3>
from Table1
where Field2 between case when field1 = 6 then '1.3.2011' else '1.3.2012' end and
case when field1 = 6 then '31.3.2011' else '22.3.2012' end

is a possible way to do this, although this probably means no index will be used for field2.

Another option is (again, I doubt an index will be used for Field2, though I don't know about recent Firebird versions):

WITH Tmp(Field1, FromDate, ToDate) as
(SELECT 6, '1.3.2011', '31.3.2011' from rdb$database)

SELECT T1.Field1, T1.Field2, T1.Field3
FROM Table1 T1
LEFT JOIN Tmp T2 ON T1.Field1 = T2.Field1
WHERE T1.Field2 BETWEEN COALESCE(T2.FromDate, '1.3.2012') AND COALESCE(T2.ToDate, '22.3.2012')

I would either choose this latter suggestion or Pauls suggestion, though for his suggestion to work with NULLs in Field2 you might have to do a minor modification:

or ( coalesce(field1, 0) <> 6

If you want an index for Field2 to be used, go for his suggestion.

HTH,
Set