LIKE clause

Robert Scheer · *nix forums beginner Joined: 16 Feb 2006 Posts: 3

Hi.

I have a query that uses the Like clause like that (only relevant
code):

....
and fl.status like (case when param1 = 1 then '%NBH%'
when param1 = 2 then '%SVH%'
when param1 = 3 then '%H%' end)
....

In fact, I would like the above SQL to show all records, if my
parameter is 3. I could not find how to do that, so I used a letter
that is common to all my values. Is this the correct way to do that?

Regards,
Robert Scheer

Robbert van der Hoorn · *nix forums beginner Joined: 29 Jun 2006 Posts: 16

"Robert Scheer" <rbscheer@my-deja.com> wrote in message
news:1153171657.974800.238020@s13g2000cwa.googlegroups.com...

G Quesnel · *nix forums addict Joined: 29 Apr 2005 Posts: 77

Note that you may get better performance by removing the case
statement. Something like;
and (param1 = 3
or (then param1 = 1 and fl.status like '%NBH%')
or (then param1 = 2 and fl.status like '%SVH%') )

If as you said, you don't need to lookup the value of fl.status when
param1 is 3.

Google