Azure SQL中Case表达式无法识别自定义计算列的问题解决

这是因为SQL的执行顺序限制：SELECT子句里定义的列别名，在同个SELECT的其他表达式中还未完成解析，所以直接引用会报错。下面给三种可行的解决方法：

方法1：重复计算逻辑（简单直接，适合短逻辑） #

把SKU和Materialnumber的计算逻辑直接写到CASE语句里，虽然有点冗余，但能快速解决问题：

SELECT
     (Dokument +'.'+ Vs) as JBR_with_version,
     Beschreibung as Description_JBR,
     ObjKey,
     SUBSTRING(ObjKey,7,5)+'-'+SUBSTRING(ObjKey,12,5)+'-'+SUBSTRING(ObjKey,17,2) AS SKU,
     CASE
         WHEN SUBSTRING(Beschreibung,1,5)+'-'+SUBSTRING(Beschreibung,7,5)+'-'+SUBSTRING(Beschreibung,13,2) != SUBSTRING(ObjKey,7,5)+'-'+SUBSTRING(ObjKey,12,5)+'-'+SUBSTRING(ObjKey,17,2) 
              AND SUBSTRING(Beschreibung,1,5)+'-'+SUBSTRING(Beschreibung,7,5)+'-'+SUBSTRING(Beschreibung,13,2) like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]' 
         THEN SUBSTRING(Beschreibung,1,5)+'-'+SUBSTRING(Beschreibung,7,5)+'-'+SUBSTRING(Beschreibung,13,2)
         ELSE 'n.a.'
     END AS Materialnumber
FROM
    dbo.SAP_JBR_SKU_Report
WHERE
     ObjKey like '[0-9]%' 

方法2：使用子查询（结构更清晰，避免重复代码） #

先在子查询里计算出SKU和Materialnumber，外层查询就可以直接引用这些别名了：

SELECT
    JBR_with_version,
    Description_JBR,
    ObjKey,
    SKU,
    CASE
        WHEN Materialnumber != SKU AND Materialnumber like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]' 
        THEN Materialnumber
        ELSE 'n.a.'
    END AS Materialnumber
FROM (
    SELECT
        (Dokument +'.'+ Vs) as JBR_with_version,
        Beschreibung as Description_JBR,
        ObjKey,
        SUBSTRING(ObjKey,7,5)+'-'+SUBSTRING(ObjKey,12,5)+'-'+SUBSTRING(ObjKey,17,2) AS SKU,
        SUBSTRING(Beschreibung,1,5)+'-'+SUBSTRING(Beschreibung,7,5)+'-'+SUBSTRING(Beschreibung,13,2) AS Materialnumber
    FROM dbo.SAP_JBR_SKU_Report
    WHERE ObjKey like '[0-9]%'
) AS SubQuery

方法3：使用CTE（公共表表达式，可读性最优） #

用CTE预先定义计算列，后续查询可以直接引用，逻辑更直观：

WITH JBRData AS (
    SELECT
        (Dokument +'.'+ Vs) as JBR_with_version,
        Beschreibung as Description_JBR,
        ObjKey,
        SUBSTRING(ObjKey,7,5)+'-'+SUBSTRING(ObjKey,12,5)+'-'+SUBSTRING(ObjKey,17,2) AS SKU,
        SUBSTRING(Beschreibung,1,5)+'-'+SUBSTRING(Beschreibung,7,5)+'-'+SUBSTRING(Beschreibung,13,2) AS Materialnumber
    FROM dbo.SAP_JBR_SKU_Report
    WHERE ObjKey like '[0-9]%'
)
SELECT
    JBR_with_version,
    Description_JBR,
    ObjKey,
    SKU,
    CASE
        WHEN Materialnumber != SKU AND Materialnumber like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]' 
        THEN Materialnumber
        ELSE 'n.a.'
    END AS Materialnumber
FROM JBRData

内容的提问来源于stack exchange，提问作者Julia Himmels