SQL Server 其中 Case then 为空 Else 不为空

发布于 2024-10-08 07:40:18 字数 607 浏览 2 评论 0原文

我有一个程序接收一个名为 @FL_FINALIZADA 的位变量。

如果它为 null 或 false，我想限制我的选择仅显示包含 null DT_FINALIZACAO 值的行。否则，我想显示包含非空 DT_FINALIZACAO 值的行。

像这样：

SELECT 
    * 
FROM 
    MyTable 
WHERE 
    ...
    AND
    (
        OPE.DT_FINALIZACAO = (
            CASE
                WHEN (@FL_FINALIZADA <> 1)
                    THEN NULL
                END
        ) OR 
        OPE.DT_FINALIZACAO IS NOT NULL
    )

在这种情况下，我收到消息：

a 中没有结果表达式 CASE 规范可以为 NULL。

我怎样才能实现这个目标？

提前致谢。

原文

I have a procedure which receive a bit variable called @FL_FINALIZADA.

If it is null or false I want to restrict my select to show only the rows that contain null DT_FINALIZACAO values. Otherwise I want to show the rows containing not null DT_FINALIZACAO values.

Something like this:

SELECT 
    * 
FROM 
    MyTable 
WHERE 
    ...
    AND
    (
        OPE.DT_FINALIZACAO = (
            CASE
                WHEN (@FL_FINALIZADA <> 1)
                    THEN NULL
                END
        ) OR 
        OPE.DT_FINALIZACAO IS NOT NULL
    )

In this case I receive the message:

None of the result expressions in a
CASE specification can be NULL.

How can I achieve this?

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一曲琵琶半遮面シ 2024-10-15 07:40:18

SELECT 
    * 
FROM 
    MyTable 
WHERE
    (ISNULL(@FL_FINALIZADA, 0) = 0
     AND
     OPE.DT_FINALIZACAO IS NULL
     )
     OR
     (@FL_FINALIZADA = 1
      AND
      OPE.DT_FINALIZACAO IS NOT NULL
     )

SELECT 
    * 
FROM 
    MyTable 
WHERE
    (ISNULL(@FL_FINALIZADA, 0) = 0
     AND
     OPE.DT_FINALIZACAO IS NULL
     )
     OR
     (@FL_FINALIZADA = 1
      AND
      OPE.DT_FINALIZACAO IS NOT NULL
     )

回复收藏 0 原文

葬﹪忆之殇 2024-10-15 07:40:18

将 AND 更改为：

AND (((@FL_FINALIZADA <> 1) AND (OPE.DT_FINALIZACAO IS NULL)) OR ( (@FL_FINALIZADA = 1) AND (OPE.DT_FINALIZACAO IS NOT NULL)))

如果位标志为 1，则 DT_FINALIZACAO 不能为空。

Change the AND to be:

AND (((@FL_FINALIZADA <> 1) AND (OPE.DT_FINALIZACAO IS NULL)) OR ( (@FL_FINALIZADA = 1) AND (OPE.DT_FINALIZACAO IS NOT NULL)))

If the bit flag is 1 then DT_FINALIZACAO can't be null.

回复收藏 0 原文

愁以何悠 2024-10-15 07:40:18

IF @FL_FINALIZADA IS NULL
    SET @FL_FINALIZADA = 0

SELECT * FROM NewsletterSubscribers 
WHERE
   (@FL_FINALIZADA = 0 AND OPE.DT_FINALIZACAO IS NULL) 
   OR 
   (@FL_FINALIZADA = 1 AND OPE.DT_FINALIZACAO IS NOT NULL)

IF @FL_FINALIZADA IS NULL
    SET @FL_FINALIZADA = 0

SELECT * FROM NewsletterSubscribers 
WHERE
   (@FL_FINALIZADA = 0 AND OPE.DT_FINALIZACAO IS NULL) 
   OR 
   (@FL_FINALIZADA = 1 AND OPE.DT_FINALIZACAO IS NOT NULL)

回复收藏 0 原文