pouvez-vous faire une AUTRE QUAND sur un CAS

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

erreur: parce que le ps n'est pas la compilation de maintenant. recevoir ce message d'erreur:
Msg 156, Niveau 15, État 1, Procédure GET_SCHEDULE_ALL_DETAIL, Ligne 18
Syntaxe incorrecte près du mot clé "QUAND".
Msg 156, Niveau 15, État 1, Procédure GET_SCHEDULE_ALL_DETAIL, Ligne 25
Syntaxe incorrecte près du mot clé 'ET'.

    ----
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

OriginalL'auteur Booksman | 2011-11-03

  1. 1

    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

    Si votre vendeur fournit un REPLACE() fonction:

    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

    OriginalL'auteur onedaywhen

  2. 8

    Pas, le RESTE est un fourre-tout. Dans votre exemple, il n'est pas clair pourquoi vous souhaitez inclure une condition dans la clause ELSE, puisque vous avez déjà vérifié l'logiquement face de condition dans la première WHEN expression.

    Cependant, de façon plus générale, vous pouvez imbriquer des CAS, des expressions, qui ressemblerait à quelque chose comme ceci:

    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

    OriginalL'auteur Dave Costa

  3. 3

    Modifier cette partie:

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

    à

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

    Il n'y a aucun point en ayant if (true) { ...} else if (false) { ... }. Il suffit de faire le reste inconditionnel.

    cette compile, mais l'application des bombes sur les disparus "évaluations"

    OriginalL'auteur Marc B

  4. 3

    Je pense que vous pouvez juste enlever que d'autre avant de le quand, et ajouter de la fin, avant que les " évaluations

       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
    en prenant de l'autre, il ne compile pas
    Je ne sais pas pourquoi tu as une erreur de compilation, mais c'est le meilleur (ou au moins la plus simple) réponse. Plusieurs WHEN états à agir comme une "AUTRE personne". msdn.microsoft.com/en-us/library/ms181765.aspx

    OriginalL'auteur Nathan Feger