Need Help on Nested CASE statement on JOIN

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 2005 Forums

Transact-SQL (2005)

Need Help on Nested CASE statement on JOIN

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2014-02-24 : 09:31:55

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

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-02-24 : 18:41:06

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.

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2014-02-27 : 06:26:01

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources