在Sql Server中使用Case语句左连接获取ERROR [英] Left Join with Case Statement in Sql Server getting ERROR

问题描述

SELECT Camp.ID,
Inst.StartDate,
detail.CertScopeValue,
Actr.Actor,
Ctype.CampaignType,
'Cert'+REPLACE(Ctype.CampaignType,' ','') AS mytable
FROM CertCampaign Camp
LEFT JOIN CertInstance Inst ON Inst.CampaignId=Camp.ID
LEFT JOIN CertScope Scope ON Scope.ID=Camp.CertScopeID
LEFT JOIN CertScopeDetail detail ON detail.CampaignId=Camp.ID
LEFT JOIN CertActor Actr ON Actr.ID=Camp.CertActorId
LEFT JOIN CertCampaignType Ctype ON Ctype.ID=Camp.CampaignTypeId
LEFT JOIN (SELECT CASE when mytable='CertUserToAccount' then CertUserToAccount)t  ON Inst.ID=t.InstanceId
(SELECT CASE mytable
when 'CertAccountToRole' then 
     LEFT JOIN CertAccountToRole
when   'CertAccountToPermission' then 
    LEFT JOIN CertAccountToPermission
when 'CertRoleToPermission' then 
    LEFT JOIN CertRoleToPermission
when 'CertUserToAccount' then 
    LEFT JOIN CertUserToAccount)TypeName  ON Inst.ID=TypeName.InstanceId
WHERE Camp.ID='46722DAF-931D-4DBE-9CD6-6FCB1B42FE94' AND detail.CertScopeID=Camp.CertScopeID

错误：

消息102，等级15，状态1，行13

'）'附近的语法不正确。

消息156，级别15，状态1，行16

关键字'JOIN附近的语法不正确'。

ERROR:
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'JOIN'.

推荐答案

第一眼看： CASE WHEN 语句不完整。必须以 END 字结束！在这两种情况下！



看看这里： CASE（SQL） [ ^ ]

On the first look: CASE WHEN statement is incomplete. Must be ended with END word! In both cases!

Have a look here: CASE (SQL)[^]

对于初学者，我怀疑第一个错误是由以下原因引起的：

For starters, I suspect the first error is caused by:

SELECT CASE when mytable='CertUserToAccount' then CertUserToAccount

，如第13行的错误所示。



'SELECT'语句别名为't'，但它从哪里选择？

as indicated by Error on line 13.

The 'SELECT' statement is aliased to 't', but where is it selecting anything from?

这篇关于在Sql Server中使用Case语句左连接获取ERROR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！