can you do a else when on the CASE statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

can you do a else when on the CASE statement?

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2011-11-03 : 14:24:50

is this a acceptable to have Case else when?

,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

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-03 : 15:58:30

I'm not sure if I'm missing what you are asking, but you can have multiple when-expressions:

CASE 
    WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' 
    WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED' 
END AS ASSESSMENTS

It'd be good form to add an ELSE clause, but if you do not and neither of those when-expressions evaluate to true, then the CASE expression will retun NULL.

http://msdn.microsoft.com/en-us/library/ms181765.aspx

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-11-03 : 16:26:02

Ok thanks, so that is not the causing not compile.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-03 : 17:21:46

Yes, THAT is causing an issue:

CASE WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' ELSE WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED' END AS ASSESSMENTS

Note the END is missing. You need to look at MSDN or BOL (Books Online) to brush up on your basic syntax.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources