sql - 你能做一个 ELSE WHEN 吗？

Question

CASE WHEN P.NURSING_UNIT is not null THEN P.NURSING_UNIT ELSE '' END NURSING_UNIT
    ,CASE WHEN P.UNIT_CODE is not null THEN P.UNIT_CODE ELSE '' END UNIT_CODE, 
    CASE WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' ELSE 
    WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED' AS ASSESSMENTS

错误:因为 sp 现在没有编译。收到此错误消息:
消息 156，级别 15，状态 1，过程 GET_SCHEDULE_ALL_DETAIL，第 18 行
关键字“WHEN”附近的语法不正确。
消息 156，级别 15，状态 1，过程 GET_SCHEDULE_ALL_DETAIL，第 25 行
关键字“AND”附近的语法不正确。

    ----

    USE [PRO]
    GO

    /****** Object: StoredProcedure [dbo].[GET_SCHEDULE_ALL_DETAIL] Script Date: 11/02/2011 14:14:50 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    alter PROC [dbo].[GET_SCHEDULE_ALL_DETAIL]
    @FACILITYKEY varchar(1000),
    @UNITSTR VARCHAR(100),
    @FromDate datetime,
    @ToDate datetime
    AS
    BEGIN

    (SELECT S.FACILITY_KEY, S.PAT_NUMBER, S.A3A_DATE_USER, M.REFERENCE_DATE ,
    RTRIM(P.LAST_NAME) + CASE WHEN RTRIM(P.FIRST_NAME) <> '' THEN ', '
    ELSE ''
    END + RTRIM(P.FIRST_NAME) PATIENT_NAME
    ,CASE WHEN P.NURSING_UNIT is not null THEN P.NURSING_UNIT ELSE '' END NURSING_UNIT
    ,CASE WHEN P.UNIT_CODE is not null THEN P.UNIT_CODE ELSE '' END UNIT_CODE, 
    CASE WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' ELSE 
    WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED' AS ASSESSMENTS
    FROM [PC].MDS_M_SCHEDULE S INNER JOIN OPTC.MD3_M_MAST M
    ON S.PAT_NUMBER=M.PAT_NUMBER
    LEFT JOIN OGEN.GEN_M_PATIENT_MAST P ON S.PAT_NUMBER = P.PAT_NUMBER
    WHERE S.PAT_NUMBER=M.PAT_NUMBER AND M.REFERENCE_DATE < GETDATE()
    AND S.A3A_DATE_USER BETWEEN @FromDate AND @ToDate
    AND S.FACILITY_KEY IN (SELECT Value FROM dbo.ListToTable(@FACILITYKEY,','))
    AND ( @UNITSTR IS NULL
    OR @UNITSTR = ''
    OR CHARINDEX(P.UNIT_CODE, @UNITSTR)% 2 = 1 ))

    UNION ALL

    (SELECT * FROM (
    (SELECT S.FACILITY_KEY, S.PAT_NUMBER, S.A3A_DATE_USER, M.REFERENCE_DATE ,
    RTRIM(P.LAST_NAME) + CASE WHEN RTRIM(P.FIRST_NAME) <> '' THEN ', '
    ELSE ''
    END + RTRIM(P.FIRST_NAME) PATIENT_NAME
    ,CASE WHEN P.NURSING_UNIT is not null THEN P.NURSING_UNIT ELSE '' END NURSING_UNIT
    ,CASE WHEN P.UNIT_CODE is not null THEN P.UNIT_CODE ELSE '' END UNIT_CODE, 'LATE' AS ASSESSMENTS
    FROM [PC].MDS_M_SCHEDULE S INNER JOIN OPTC.MD3_M_MAST M
    ON S.PAT_NUMBER=M.PAT_NUMBER
    LEFT JOIN OGEN.GEN_M_PATIENT_MAST P ON S.PAT_NUMBER = P.PAT_NUMBER
    WHERE M.REFERENCE_DATE < GETDATE() AND S.A3A_DATE_USER BETWEEN @FromDate AND @ToDate
    AND ( @UNITSTR IS NULL
    OR @UNITSTR = ''
    OR CHARINDEX(P.UNIT_CODE, @UNITSTR)% 2 = 1 )
    ) --Started

    UNION ALL

    (SELECT S.FACILITY_KEY, S.PAT_NUMBER, S.A3A_DATE_USER, NULL AS REFERENCE_DATE,
    RTRIM(P.LAST_NAME) + CASE WHEN RTRIM(P.FIRST_NAME) <> '' THEN ', '
    ELSE ''
    END + RTRIM(P.FIRST_NAME) PATIENT_NAME
    ,CASE WHEN P.NURSING_UNIT is not null THEN P.NURSING_UNIT ELSE '' END NURSING_UNIT
    ,CASE WHEN P.UNIT_CODE is not null THEN P.UNIT_CODE ELSE '' END UNIT_CODE, 'LATE' AS ASSESSMENTS
    FROM [PC].MDS_M_SCHEDULE S INNER JOIN OPTC.MD3_M_MAST M
    ON S.PAT_NUMBER=M.PAT_NUMBER
    LEFT JOIN OGEN.GEN_M_PATIENT_MAST P ON S.PAT_NUMBER = P.PAT_NUMBER
    WHERE S.PAT_NUMBER NOT IN (SELECT M.PAT_NUMBER FROM [PC].MD3_M_MAST M)
    AND S.A3A_DATE_USER < GETDATE() AND S.A3A_DATE_USER BETWEEN @FromDate AND @ToDate
    AND ( @UNITSTR IS NULL
    OR @UNITSTR = ''
    OR CHARINDEX(P.UNIT_CODE, @UNITSTR)% 2 = 1 )) --Not Started
    ) LATE
    WHERE FACILITY_KEY IN (SELECT Value FROM dbo.ListToTable(@FACILITYKEY,',')))

    END

    GO

Answer 1

不，ELSE 是一个包罗万象的东西。在您的示例中，不清楚为什么要在 ELSE 子句中包含条件，因为您已经在第一个 WHEN 中检查了逻辑相反的条件。表达。

但是，更一般地，您可以嵌套 CASE 表达式，它看起来像这样:

CASE
  WHEN m.sign_date IS NOT NULL THEN 'COMPLETED'
  ELSE
    CASE WHEN m.start_date IS NOT NULL THEN 'IN PROGRESS' ELSE 'NOT STARTED' END
END