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 error: because the sp is not compiling now. getting this error message: Msg 156, Level 15, State 1, Procedure GET_SCHEDULE_ALL_DETAIL, Line 18 Incorrect syntax near the keyword 'WHEN'. Msg 156, Level 15, State 1, Procedure GET_SCHEDULE_ALL_DETAIL, Line 25 Incorrect syntax near the keyword '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 4 Answers
No, ELSE is a catch-all. In your example, it's not clear why you would want to include a condition in the ELSE clause, since you've already checked the logically opposite condition in the first WHEN expression.
However, more generally, you can nest CASE expressions, which would look something like this:
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 I think you can just remove that else before the when, and add an end before 'as assessments'
coalesce(P.NURSING_UNIT , '') NURSING_UNIT, -- you can use coalesce here too coalesce(P.UNIT_CODE, '') UNIT_CODE, CASE WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED' END AS ASSESSMENTS Change this part:
CASE WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' ELSE WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED' to
CASE WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' ELSE 'UNCOMPLETED' END There's no point in having if (true) { ...} else if (false) { ... }. Just make the else unconditional.
Standard SQL:
COALESCE(P.NURSING_UNIT, '') AS NURSING_UNIT, COALESCE(P.UNIT_CODE, '') AS UNIT_CODE, CASE WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED' ELSE 'COMPLETED' END AS ASSESSMENTS If your vendor provides a REPLACE() function:
COALESCE(P.NURSING_UNIT, '') AS NURSING_UNIT, COALESCE(P.UNIT_CODE, '') AS UNIT_CODE, COALESCE(REPLACE(M.SIGN_DATE, M.SIGN_DATE, 'COMPLETED'), 'UNCOMPLETED') AS ASSESSMENTS 
