如何在查询的Coldfusion查询中测试空/空字符串？ [英] How to test for null/empty string in Coldfusion query of query?

问题描述

我一直在网上寻找解决方案，但都没有解决。我在Coldfusion查询中有一列 date_hired。如果我对查询执行cfdump，则将日期显示为日期（如果没有），则显示为[空字符串]。查询中有8条记录；有些有date_hired的日期，有些则没有。如果我尝试在此结果集上执行q的q：

I've been looking online for a solution, but none's come up. I have a column in a coldfusion query, "date_hired". If I do a cfdump of the query, it shows as a date if its a date or as [empty string] if not. There are 8 records in the query; and some have dates for date_hired and some don't. If I try to do a q of q on this resultset:

SELECT date_hired
    FROM myQuery
    WHERE date_hired = ''

我收到一条错误消息：执行=时比较异常。
不支持的类型比较例外：=运算符不支持以下类型之间的比较：
左侧表达式类型= NULL。
右侧表达式类型= STRING。

I get an error message saying: Comparison exception while executing =. Unsupported Type Comparison Exception: The = operator does not support comparison between the following types: Left hand side expression type = "NULL". Right hand side expression type = "STRING".

好的，所以我将查询更改为：

Okay, so I change my query to:

SELECT date_hired
    FROM myQuery
    WHERE date_hired IS NOT NULL

，但它返回所有8行，即使cfdump中date_hired为[空字符串]的行也是如此。同样，如果将where子句更改为 where date_hired IS NULL，则会返回0行，甚至没有[空字符串]的行。

but it returns all 8 rows, even the ones where date_hired is [empty string] in the cfdump. Likewise, if I change the where clause to "where date_hired IS NULL", I get 0 rows returned, not even the [empty string] ones.

我在亏损。 ISNULL（）和LEN（）不能在q个q中使用。幸运的是，如果我对查询执行cfloop并输出isDate（date_hired），它的确会在应返回的地方返回true，而在应返回的地方返回false。因此，我可以对查询执行cfloop并即时构建另一个查询，但这似乎是一种回旋的方式，可以完成一些不难的事情。在where子句中可以使用一些条件吗？谢谢-CM

I'm at a loss. ISNULL() and LEN() can't be used in a q of q's. Fortunately, if I do a cfloop of the query and output isDate(date_hired), it does return true where it should and false where it should. So I can cfloop over the query and construct another one on the fly, but that seems like a roundabout way to do something that shouldn't be hard. Is there some conditional I can use in the where clause that will work here? Thanks - CM

推荐答案

感谢Alex的答复-我终于明白了。在这种情况下，我的查询通过cfquery提取，然后稍后使用queryAddColumn（）向其中添加一些列。这些列之一是date_hired。如果我尝试使用cfloop路由，则无论我将该列的值设置为什么（日期或字符串），CF都将其保留为NULL类型（并且不适用于IS NULL / IS NOT NULL）。因此，经过进一步研究，我尝试在where子句中使用Cast（）函数：

Thanks Alex for your reply - I finally figured it out. In this case, my query is pulled via cfquery, then I add some columns to it later using queryAddColumn(). One of those columns is date_hired. If I try to go the cfloop route, no matter what I set the values of that column to (a date or a string), CF keeps it as type NULL (and won't work with IS NULL/IS NOT NULL). So after some further research, I tried using the Cast() function in my where clause:

<cfquery name="numberHired" dbtype="query">
        select count(*)
        from myQuery
        where CAST(date_hired AS varchar) <> '' 
  </cfquery>

它就像一个护身符。

这篇关于如何在查询的Coldfusion查询中测试空/空字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！