Query of Queries not matching empty string

Question

I have a the output from a stored procedure on a SQL Server that I am running a query of queries on in CF9. I don't have any access to the stored procedure to make any changes, but I can output the contents.

My problem is that my QofQ is matching on several fields, one of which has a record that has an empty string in one of the fields that I'm matching on. The recordset should return that record, but it is not part of my recordset.

<cfquery name="test" dbtype="query">
SELECT * 
FROM dataLimited
WHERE market = <cfqueryparam value="" cfsqltype="cf_sql_varchar">
</cfquery>

This returns zero records, even though I can see the record in the dump of the stored procedure.

When I dump the results from the SP, the value is [empty string] — Nathaniel
– Nathaniel, Commented May 30, 2016 at 17:12

Leigh · Accepted Answer · 2016-05-30 18:18:37Z

5

Are you sure that the field has an empty string, and not null? CF does not do a good job of distinguishing between NULL and empty string, but query of queries still respects the difference. i.e. Use the expression:

WHERE market IS NULL

edited May 30, 2016 at 18:18

Leigh

28.9k10 gold badges58 silver badges109 bronze badges

answered May 30, 2016 at 2:07

Tim Jasko

1,5421 gold badge8 silver badges13 bronze badges

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

3 Comments

Nathaniel Over a year ago

OK, this turns out to be the issue, but I'm not sure how to solve it. If my where clause is: WHERE market IS NULL then I get records. Considering the limitations on QoQ, is there a way to turn the NULL values into empty strings in the QofQ?

Leigh Over a year ago

That can only be done within the db query/procedure OR by manually looping through the query and overwriting each value. However, if WHERE Col IS NULL returns the expected records, why do you need to convert them to empty strings? Just use is null. If for some reason you want to pick up both empty strings and nulls use both filters in the WHERE condition with the OR operator.

Tim Jasko Over a year ago

@Nathaniel, you can use coalesce(market, '') as market in your initial database query to turn those nulls into empty strings, but that is probably not the best approach. I would recommend sticking with where market is null or market = ''

Nathaniel · Accepted Answer · 2016-05-30 18:59:26Z

0

I'm looping over my QofQ to do subsequent QofQs, creating dynamic rows in a table. So my subsequent QofQs will have WHERE clauses that will match on the (possibly) NULL value or on a Non-NULL value. My solution has been to check the len() of the value and then in the subsequent query use:

<cfif len(data.market)> 
AND market = <cfqueryparam value="#data.market#" cfsqltype="cf_sql_varchar"> 
<cfelse> 
AND market IS NULL 
</cfif>

answered May 30, 2016 at 18:59

Nathaniel

212 bronze badges

1 Comment

Leigh Over a year ago

Okay, that part was not clear from the initial question. looping over my QofQ to do subsequent QofQs Not related to the original question, but try and avoid querying within a loop when possible. Since it requires executing/building a brand new query for each row, it is very inefficient and tends to scale poorly.

Collectives™ on Stack Overflow

Query of Queries not matching empty string

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related