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

3
votes

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

2
sqlcoldfusionqoq
Could you include the relevant part of the table's schema in your post? And how does the query before the QoQ look like? Also, what version of ColdFusion are you using? - Alex
Does it depend on the database type and driver used? Just today, I had a case where I had to use IS NULL to test for empty in a query of query over an Oracle result. Ben Nadel had it back in 2006, already: bennadel.com/blog/… - Bernhard Döbler

2 Answers

4
votes

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.

1
votes

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.