3

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

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Cmaso
  • 1,095
  • 1
  • 10
  • 23
  • 1
    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 Feb 11 '16 at 01:18
  • 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: https://www.bennadel.com/blog/112-coldfusion-query-of-queries-uses-null-values-returns-empty-strings.htm – Bernhard Döbler Oct 29 '20 at 20:09

2 Answers2

4

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.

Cmaso
  • 1,095
  • 1
  • 10
  • 23
  • Another approach, without using loops, might be to convert the valueList of the date_hired column to an array and then back to a list. I understand that gets rid of the empty elements. Then get the length of the resulting list to get the same number as your q of q. – Dan Bracuk Feb 11 '16 at 03:42
  • You mention that you are adding date_hired to the query object after you run it? That means it must be an array. Whatever code you are using to build that array could be enhanced to include the number of not null values. – Dan Bracuk Feb 11 '16 at 03:45
1

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.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43