Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL 2000 and Case Statements

Status
Not open for further replies.

lyric0n

Technical User
Dec 28, 2005
74
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
 
Case has two syntax flavors:

Code:
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:

Code:
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]
 
>>>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 [lol]


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

Ignorance of certain subjects is a great part of wisdom
 
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 [ponder]
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
 
[rofl]

-George

"the screen with the little boxes in the window." - Moron
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top