Subject | RE: [firebird-support] Conditional/alternative where clauses |
---|---|
Author | Svein Erling Tysvær |
Post date | 2011-03-23T08:10:18Z |
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
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