在 SQL 比较中比较 null [英] comparing null in SQL comparisons

问题描述

我正在用 VB6 创建一个前端，我的数据库是 Sybase.使用 DSN 我创建了一些小的 exe 来填充网格中的报告.它工作正常.

I am creating a front end with VB6 and my database is Sybase. Using DSN I have created some small exe's to populate reports in grid. It was working fine.

但是，如果我使用以下查询，我只会得到小时和已回答的数据.如果我在 SQL Query 中执行查询，则完整数据即将到来.

However if I use the below query I am getting only hour and Answered data. If I execute the query in SQL Query complete data is coming.

我相信总和(如果在 VB6 中不起作用，请指导我替代.

I believe the sum(case when will not work in VB6, please guide me for an alternate.

"select datepart (hh, callstartdt) as Hour, " _
    & " count(seqnum) as Anaswered," _
    & " sum(case when user_id <> NULL then 1 else 0 end) as answered_calls ," _
    & " sum(case when user_id <> NULL and  datediff (ss, callstartdt, QueueEndDt) <= 20 then 1 else 0 end) , " _
    & " sum(case when user_id = NULL then 1 else 0 end), " _
    & " sum(case when user_id <> NULL and datediff (ss, callstartdt, QueueEndDt) <= 20 then 1 else 0 end)  / count(seqnum), " _
    & " sum(Case when user_id <> NULL then 1 else 0 end ) / count(seqnum) from acdcalldetail " _
    & " where callstartdt between '" & fromDt & "' and '" & toDt & "' " _
    & " and service_id not in (37,39,47,51,57,58,96,215,374,375) " _
    & " group by datepart (hh, callstartdt) " _
    & " order by datepart (hh, callstartdt)"

推荐答案

当 user_id <> 时不能使用 空.您必须使用 user_id Is Null 或 user_id Is Not Null.任何 = 或 <> 到 Null 都会导致 Unknown，对于 Case 表达式，它被视为 false.

You cannot use when user_id <> Null. You must use user_id Is Null or user_id Is Not Null. Anything = or <> to Null results in Unknown which is treated as false for the Case expression.

这篇关于在 SQL 比较中比较 null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！