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 = ''
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
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.
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
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>
and it works like a charm.