sql 2005 case语句 - 列名无效

Can someone tell me why I get error: Msg 207, Level 16, State 1, Procedure ExtractPDP4FromPDP, Line 21 Invalid column name 'ContainsEX'.

有人能告诉我为什么会收到错误:消息207,级别16,状态1,过程ExtractPDP4FromPDP,行21无效的列名称'ContainsEX'。

when executing the following stored procedure.

执行以下存储过程时。

CREATE PROCEDURE ExtractPDP4FromPDP 
    -- Add the parameters for the stored procedure here
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT PDP.*, LEFT(PDPCode,7) AS PDP7, PDP.Obsolete, 
        PDP.InvestorPDP, PDP.OnlineReport, PDP.ClientSpecific, 
        ContainsEX = CASE 
    WHEN(CHARINDEX(Left(PDPCode,5),'EX')>0) THEN 'True'
    ELSE 'False'
END, PDP4 =
CASE 
    WHEN ContainsEX = 'True' THEN 'E' & SUBSTRING(pdpcode,5,3)
    ELSE SUBSTRING(pdpcode,6,3)
END
FROM PDP
WHERE (((PDP.Obsolete)='False') AND ((PDP.InvestorPDP)='True') AND 
    ((PDP.OnlineReport)='False') AND ((PDP.ClientSpecific)='False'));  
END
GO

Thank You in advance

先感谢您

3 个解决方案

#1

Sql Server does not allow you to refer to fields on the same level. You'd have to create a subquery, like:

Sql Server不允许您引用同一级别的字段。你必须创建一个子查询,如:

select *,
    PDP4 = CASE 
        WHEN ContainsEX = 'True' THEN 'E' & SUBSTRING(pdpcode,5,3)
        ELSE SUBSTRING(pdpcode,6,3)
    END
from (
    select *, LEFT(PDPCode,7) AS PDP7, PDP.Obsolete, 
        PDP.InvestorPDP, PDP.OnlineReport, PDP.ClientSpecific, 
        ContainsEX = CASE 
            WHEN(CHARINDEX(Left(PDPCode,5),'EX')>0) THEN 'True'
            ELSE 'False'
        END
    from PDP
    WHERE (((PDP.Obsolete)='False') AND ((PDP.InvestorPDP)='True') AND 
   ((PDP.OnlineReport)='False') AND ((PDP.ClientSpecific)='False'))
) sub

#2

You cannot use a column in a statement immediately after defining it.

定义后,不能立即在语句中使用列。

One alternative is to use stacked CTEs to build up your expressions or use nested queries:

一种替代方法是使用堆叠CTE来构建表达式或使用嵌套查询:

WITH cte1 AS (
    SELECT x, x AS y
    FROM t
),cte2 AS (
    SELECT x, y, x + y AS z
    FROM cte1
)
SELECT x, y, z
FROM cte2

#3

Your case statement is not correct. CASE WHEN 'value' = 'value' THEN 'do something like CONTAINEX = 'grandma' END

您的案例陈述不正确。情况当'value'='value'然后'执行类似CONTAINEX ='grandma'END的事情

#1