SQL 2000 and Case Statements

[lyric0n]

Hello, I have the following data and I need to have the result listed below, but when I try to use case statements, it fails. Could someone take a look and see if they can see what I am doing wrong?

Table1
IDKey
1
2
3
4

Table2
IDKey | Name
1 | John
2 | Jane

Table3
IDKey | Name
3 | Joe
4 | Sally

Result
IDKey | Name
1 | John
2 | Jane
3 | Joe
4 | Sally

SELECT Table1.IDKey,
CASE Table2.Name
WHEN IS NULL THEN Table3.Name
END AS IDKeyName
FROM Table1 LEFT OUTER JOIN Table1.IDKey = Table3.IDKey LEFT
OUTER JOIN Table2.IDKEY ON Table1.IDKey = Table2.IDKey

Thanks, Chris

 

[SQLDenis]

you don't need case

COALESCE( Table2.Name,Table3.Name) will do the same

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[lyric0n]

Thanks!

 

[ESquared]

Case has two syntax flavors:

CASE Value WHEN EqualExpression THEN ... ELSE ... END
CASE WHEN ExpressionEvaluatingToTrue THEN ... ELSE ... END

You've confused the two and are essentially testing for the condition "Table2.Name = IS NULL". Just use the second syntax:

CASE
   WHEN Table2.Name IS NULL THEN Table3.Name
   ELSE Table2.Name
END

For what it's worth, the upper syntax flavor is converted to the lower one by the optimizer. It's just a shortcut like IN is for a bunch of OR statements.

But Borislav is of course right that Coalesce is the best answer here.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[AlexCuse]

>>>But Borislav is of course right that Coalesce is the best answer here.

Denis, you didn't tell us (well, all of us anyway) that you changed your name


[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

oops

all the threads are blending together...

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[SQLDenis]

Pleased to meet you
Hope you guess my name
Please allow me to introduce myself
Im a man of SQL and Code
I've been around for long, long years
Pleased to meet you
Hope you guess my name
But whats puzzling you
Is the nature of my game


I am known by many names, however Borislav is not one of them ;-)


BTW no offense taken

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

-George

"the screen with the little boxes in the window." - Moron

 

[AlexCuse]

Someone is letting the programmers get a little loopy this Friday...



[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom