using a case statement with Where clause

Question

Post reply

using a case statement with Where clause

Donald Leaderach

SSC-Addicted

Points: 478
More actions
March 5, 2008 at 6:20 am

#95715

I am trying to create a Where clause that will allow me to selectively test fields based on whether or not they are Null.
where 1=1 and
and (b.Director = case
when not a.Director IS NULL
then Director
end)
My goal is to only test b.Director = Director if Director is not null.
If it is Null then the test would not occur.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Donald Leaderach SSC-Addicted Points: 478 More actions · Answer 1

I received an answer on another forum:

WHERE

CASE

WHEN A.Director IS NOT NULL AND A.Director = B.Director THEN 1

WHEN A.Director IS NULL THEN 1

ELSE 0

END = 1

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 2

You can do this without a case statement. Try this...

Where

isnull( a.director, 1) = 1 or a.director = b.director

XPSCodes-667952 SSChasing Mays Points: 603 More actions · Answer 3

A lil change:

Where isnull(a.director, b.director) = b.director

--------------------------------------------------------------------------------------
Save our world, its all we have! A must watch video Pale Blue Dot[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

I did give a reply in another forum similar to your example, but that was for the general case of selective test criteria.

For the specific case of collapsing null values, Lifer is right, [font="Courier New"]isnull()[/font] is better (and [font="Courier New"]coalesce()[/font] is even better :smooooth: ). You can also generalize their application slightly to cover many cases where you want Null-avoidance.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 5

Hi,

If you are apply any functions like(i.e. ISNULL or COALESCE) this then the query will not give you good performance. Please be carefull on that.

Thanks -- Vj

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Yeah. It's a shame really, since so many good functions could easily be macro-ed out to easily search-able expressions in the WHERE clause.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Donald Leaderach SSC-Addicted Points: 478 More actions · Answer 7

Thanks for all of the help.

I have resolved the issue.

CASE

WHEN Director IS NOT NULL AND Director = B.Director

THEN 1

WHEN Director IS NULL THEN 1

ELSE 0

END = 1

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

glad we could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]