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

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
– Alex, Commented Feb 11, 2016 at 1: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: bennadel.com/blog/… — Bernhard Döbler
– Bernhard Döbler, Commented Oct 29, 2020 at 20:09

Cmaso · Accepted Answer · 2016-02-11 02:01:25Z

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.

answered Feb 11, 2016 at 2:01

Cmaso

1,1132 gold badges12 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Bracuk Over a year ago

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 Over a year ago

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 · Accepted Answer · 2016-02-11 11:56:39Z

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.

answered Feb 11, 2016 at 11:56

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Comments

Krish Kot · Accepted Answer · 2024-07-16 19:54:31Z

This is how I handle the empty rows coming from the spreadsheet. The above mentioned answers didnt really work for me and hence had to come up with my own. We use this when processing all of our bulk file uploads.

    <!--- NOTE: 
        Any empty rows captured should be ignored.
        We need to wrap any special key words in square brackets ex: [Year] or [local] when performing QoQs 
    --->
    <cfset local.sColList = arguments.columnsList />
    <cfloop list="local,Year" index="local.sKeyWord">
        <cfif ListFindNoCase(local.sColList,local.sKeyWord) GT 0>
            <cfset local.sColList = replaceNoCase(local.sColList, local.sKeyWord, "[#local.sKeyWord#]") />
        </cfif>
    </cfloop>
    <!--- Do a QoQ as the best performant solution over loops to remove the empty rows. --->
    <cfset local.sWhereClause = "" />
    <cfloop list="#local.sColList#" index="local.sCol">
        <cfset local.sWhereClause &= "(" & local.sCol & " <> '' AND " & local.sCol & " IS NOT NULL) OR " />
    </cfloop>
    <!--- Appending the below condition to handle the extra "OR" that gets appended above after the last list element --->
    <cfset local.sWhereClause &= "1 = 0" />
    <!--- Performing a Query of Query to discard the empty rows where all columns were empty --->
    <cfquery name="local.qSSData" dbtype="query">
        SELECT #local.sColList# FROM arguments.spreadSheetQuery
        WHERE #local.sWhereClause#
    </cfquery>

    <cfreturn local.qSSData />
</cffunction>

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related