|Subject||RE: [firebird-support] sp case statement|
|Author||Svein Erling Tysvær|
>I guess my question wasn't very clear. What I'm trying to do is have conditional logic in the where clause.I think there is a very simple solution to this, Daniel, though you have to think slightly different:
>Select * from table T
> Case :input_code
> when 'A' then
> T.somefield = 'A'
> when 'B' then
> t.somefield = 'B'
> else /* else we want both types */
> t.somefield = 'A' or t.somefield = 'B'
select * from table T
where t.somefield in ('A', 'B')
and (coalesce(:input_code, '') <> 'A' or T.somefield = 'A')
and (coalesce(:input_code, '') <> 'B' or t.somefield = 'B')
I used COALESCE since I wasn't certain whether :input_code could be NULL or not.
>This is just a simplified example. There are multiple input parameters and the where clause will getHopefully, simplified example doesn't mean that my suggested solution isn't viable. And, yes, I think you have to live with having the stored procedure check every record that has A or B in t.somefield as long as you do not want separate queries depending on the :input_code.
>more complex. Do I have to live with having the stored procedure check every record in my example?