Interbase：字符串“”的转换错误
我正在运行 IB2009，并且尝试计算特定字段既不是 NULL 也不是空的记录数：
SELECT COUNT(A.ID) FROM MYVIEW A
WHERE ((A.VARCHARFIELD1 IS NOT NULL) OR (A.VARCHARFIELD1 <> ''))
其中 MYVIEW 是 VIEW，MYVIEW.ID 是 INTEGER，而 MYVIEW.VARCHARFIELD1 是 VARCHAR(18) 。
我收到错误消息
第 1 行出错，字符串“”转换错误
我不太明白，因为当我删除 COUNT() 函数时，查询执行得很好。有谁知道我做错了什么？谢谢！
I'm running IB2009 and I'm trying to count the number of records where a specific field is neither NULL nor empty:
SELECT COUNT(A.ID) FROM MYVIEW A
WHERE ((A.VARCHARFIELD1 IS NOT NULL) OR (A.VARCHARFIELD1 <> ''))
where MYVIEW is a VIEW, and MYVIEW.ID is an INTEGER, while MYVIEW.VARCHARFIELD1 is a VARCHAR(18).
I'm getting the error message
Error at line 1, conversion error from string ""
which I don't really understand, since when I drop the COUNT()-function, the query executes nicely. Do anyone know what I'm doing wrong? Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在这种情况下，我认为使用 COUNT() 没有任何问题。尽管这并不重要，但您可以尝试删除 WHERE 子句中的所有括号。您也可以尝试使用 COUNT(*) 而不是 COUNT(A.ID) 只是为了看看它是否会给出不同的结果。
无论如何，根据您对查询意图的描述，我认为查询不会执行您的预期操作。如果查询应该返回字段既不为 NULL 也不为空的行，我相信您的 WHERE 子句应该
按照最初编写的那样使用“OR”，我认为您将返回表中 A.VARCHARFIELD1 所在的每一行NOT NULL，因为“字段 NOT NULL”谓词允许所有非 NULL 值，并且由于两个谓词通过 OR 连接，因此第二个谓词无关紧要。
我希望这有帮助。
I don't see anything wrong with using COUNT() in this case. Although it shouldn't matter you might try dropping all the parentheses in your WHERE clause. You also might try using COUNT(*) instead of COUNT(A.ID) just to see if it gives you different results.
In any case, based on your description of the intent of the query I don't think that the query is going to do what you intended. If the query is supposed to return rows where the field is neither NULL nor empty I believe your WHERE clause should be
With the 'OR' in there as originally written I think you'll get back every row in the table where A.VARCHARFIELD1 is NOT NULL, as the "field NOT NULL" predicate will allow in all non-NULL values, and since the two predicates are joined by an OR the second predicate won't matter.
I hope this helps.