不返回任何内容时，t-sql会强制显示一条消息。使用CTE和CASE

debugcn 发表于 Dev

布赖恩·卡斯科内（Brian Cascone）

我撤回了一些将在某些报告中使用的数据。大多数情况下，这将不返回任何数据，当不返回任何数据时，我希望最终用户看到一条消息，而当有数据时，我希望返回数据元素和其他消息。我将不包括CTE部分，仅包括CASE。应该足够了。我不确定为什么这行不通，应该是一个简单的案例。

SELECT Case when Sum(1) IS NULL  THEN 
    'NO DUPES MISSING INFO'
ELSE 
        CTE.ClaimNum + ' Claim is missing, Please populate'  
END AS MissingClaiminfo

FROM CTE

WHERE ClaimCheck > 0
OR CLAIMFormatCk > 0
OR CLAIMIdCheck > 0
OR CLAIM_DupID_Match > 0
Group by CTE.ClaimNum

返回的内容没有MissingClaimInfo的记录或消息。

任何帮助表示赞赏。

编辑---完整查询如下：

WITH Nasco_CTE (
                    CoClaimNum
                    ,ClaimNum
                    ,ClaimCheck
                    ,CLAIMFormatCk
                    ,CLAIMIdCheck
                    ,CLAIMID_DupID_Match
                )

AS
    (

        SELECT

             CW.User047 AS CoClaimNum
            ,CW.ClaimNum AS ClaimNum
            ,CASE WHEN Left(CW.User047,1) <> 0 THEN 1 ELSE 0 END As ClaimCheck  --     Checks for leading zeroes. 
            ,CASE WHEN SubString(CW.User047,LEN(LEFT(CW.User047, CharIndex('-    ',CW.User047) )),1) <> '-' THEN 1 ELSE 0 END AS CLAIMFormatCk   --- Checks for '-'
            ,CASE WHEN NASC.NascoClaimId = '' THEN 1 ELSE 0 END AS CLAIMIdCheck
            ,CASE WHEN NASC.NascoClaimId <> ECW.NascoClaimId THEN 0 ELSE 1 END AS     CLAIMID_DupID_Match

        FROM Screens.dbo.vClaimsWHSE CW
        LEFT JOIN [Screens].dbo.[vClaimsWHSE_RootReason] CWRR
            ON CW.ClaimCode = CWRR.Reason
        LEFT JOIN [Support].[dbo].[NascoDupID_Crosswalk] NASC
            ON  NASC.ClaimNum = SUBSTRING(CW.User047,0,CHARINDEX('-',CW.[User047]))
            AND NASC.LineSequenceNum =  SUBSTRING(CW.User047,CHARINDEX('-',CW.     [User047])+1,CHARINDEX('-',CW.[User047]))
        LEFT JOIN [Data].[dbo].[ExportCrosswalk] ECW
            ON CW.User001 = ECW.ClaimNum 
            AND CW.User002 = ECW.LineSequenceNum
        WHERE CW.user003 IN ('NC','RevRecon') 
            And NASC.ClaimId IS NOT NULL
    )


SELECT Case when COUNT(Nasco_CTE.ClaimNum) =''  THEN 
     'NO DUPES MISSING INFO'
ELSE 
     Nasco_CTE.ClaimNum + ' Claim is missing a DupNascoID.  Please check check that     USER047 is populated correctly: ClaimNum-LineNum EX: 020132952416600-1'  
END AS MissingNascoId 
FROM Nasco_CTE

WHERE ClaimCheck > 0
OR ClaimCheck > 0
OR CLAIMIdCheck > 0
OR CLAIMID_DupID_Match > 0
Group by Nasco_CTE.ClaimNum

请注意，当我从ELSE部分删除Nasco_CTE.ClaimNum +时，该代码有效。并删除分组依据。

Damien_The_Unbeliever

再创建几个CTE：

;WITH Nasco_CTE (/* Columns */)

AS
    (/* Definition */
), InitialResults as (
    SELECT CTE.ClaimNum + ' Claim is missing, Please populate'  
      AS MissingClaiminfo, 0 as Ord

   FROM CTE

   WHERE ClaimCheck > 0
   OR CLAIMFormatCk > 0
   OR CLAIMIdCheck > 0
   OR CLAIM_DupID_Match > 0
   Group by CTE.ClaimNum --This might not be needed any more, I think
   UNION ALL
   SELECT 'NO DUPES MISSING INFO',1
), Ranked as (
    SELECT *,RANK() OVER (ORDER BY Ord) as rk
)
SELECT * from Ranked where rk = 1

Ord 1如果不存在Ord等于0-的行，则只会产生具有该行的行，因此，您将获得所需的结果集或仅显示一行NO DUPES MISSING INFO。