CASE not working with ON clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CASE not working with ON clause

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-10-25 : 14:20:48

Hi All,

The following query is not updating any records.

UPDATE #RTMItems
SET DSID = ID
FROM LocalWorkItem WI
INNER JOIN LocalEdges LRE
ON WI.ID = CASE WHEN LRE.FromLo = 0 THEN LRE.HiID ELSE LRE.LoID END
INNER JOIN #RTMItems A
ON A.FSID = CASE WHEN LRE.FromLo = 1 THEN LRE.LoID ELSE LRE.HiID END
WHERE WI.Fld10057 = 'Operational'

Can any one tell me why the CASE is not working in the query? Or if there is a better way to write this query?

Thanks,
-P

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-25 : 15:00:14

Sure

Fix your database

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-10-25 : 15:03:43

It it TFS database from Microsoft which I can not modify.

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-10-25 : 15:04:07

TFS - team foundation server

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-25 : 15:18:34

Are there any NULLs in LRE.FromLo?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-25 : 15:28:59

If you need to do a conditional JOIN, do not use CASE expressions; use multiple LEFT OUTER JOINS.

see:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-10-25 : 15:48:38

There are 0 or 1 in FromLo column

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-10-25 : 15:56:41

Thanks Jeff,

But I am not quite clear on what I should be doing to correct the problem. Can you please provide some more details?

Thanks,
-P

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-25 : 17:53:07

Just do left outer joins to both tables, and then use CASE in the SELECT clause -- not the join clause -- to return data from the appropriate table. See the article for details.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources