How to test for null/empty string in Coldfusion query of query?

Question

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

Answer 1

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.

Answer 2

Here is another way that might run faster. It takes advantage of the fact that query columns can be treated as arrays.

<cfquery name="dbQuery" datasource="oracleDB">
select trunc(sysdate) theDate
from dual
union
select null theDate
from dual
union
select trunc(sysdate - 1) theDate
from dual
</cfquery>

Notice that there are two values that are not null. This:

<cfdump var="#Listlen(ArrayToList(dbQuery['theDate']))#">

returns 2, which is the number you sought.

This method is probably more efficient than using Q of Q. However, the Q of Q method is more readable, which is also important.