CODEWITHSUNDEEP
sqlcoldfusionqoq
Cmaso
Cmaso

Reputation: 1113

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

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

Upvotes: 3

Views: 4992

Answers (3)

Krish Kot
Krish Kot

Reputation: 1

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>

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

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.

Upvotes: 1

Cmaso
Cmaso

Reputation: 1113

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.

Upvotes: 4

Related Questions