SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need Help on Nested CASE statement on JOIN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vision.v1
Yak Posting Veteran

71 Posts

Posted - 02/24/2014 :  09:31:55  Show Profile  Reply with Quote
Hi,

I am getting error when trying below JOIN with CASE statement can any one help?


If maxLevelID = 1 then only a.language = b.levelLanguageID should be satisfied else only a.client = b.level1 condition is enough



UPDATE
a
SET
a.clientEntityID = b.level1EntityID
FROM
#tempInternalExternalHierarchyGoals a
JOIN
results.dbo.ctlHierarchyAll_20140219 b
ON
a.client = b.level1
AND
CASE
WHEN maxLevelID = 1 THEN
CASE
WHEN a.language = b.levelLanguageID THEN 1
ELSE 0
END
ELSE 1
END

Edited by - vision.v1 on 02/24/2014 09:33:43

nagino
Yak Posting Veteran

Japan
65 Posts

Posted - 02/24/2014 :  18:41:06  Show Profile  Reply with Quote
Hope this help

UPDATE 
	a
SET
	a.clientEntityID = b.level1EntityID
FROM #tempInternalExternalHierarchyGoals a
JOIN results.dbo.ctlHierarchyAll_20140219 b
	ON (maxLevelID = 1 AND a.language = b.levelLanguageID)
	OR ((maxLevelID != 1 OR maxLevelID IS NULL) AND a.client = b.level1)

--If maxLevelID has NOT NULL Constraint
UPDATE 
	a
SET
	a.clientEntityID = b.level1EntityID
FROM #tempInternalExternalHierarchyGoals a
JOIN results.dbo.ctlHierarchyAll_20140219 b
	ON (maxLevelID = 1 AND a.language = b.levelLanguageID)
	OR (maxLevelID != 1 AND a.client = b.level1)



-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

vision.v1
Yak Posting Veteran

71 Posts

Posted - 02/27/2014 :  06:26:01  Show Profile  Reply with Quote
Thanks for reply....I tried in different way as below its worked for me..

UPDATE
a
SET
a.clientEntityID = b.level1EntityID
FROM
#tempInternalExternalHierarchyGoals a
JOIN
results.dbo.ctlHierarchyAll b
ON
a.client = b.level1
AND
(CASE
WHEN maxLevelID = 1 AND a.language = b.level1LanguageID AND a.channelID = b.level1ChannelID THEN 1
WHEN maxLevelID <> 1 THEN 1
ELSE 0
END) = 1
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000