1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nathaniel
  • 21
  • 2

2 Answers2

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
Leigh
  • 28,765
  • 10
  • 55
  • 103
Tim Jasko
  • 1,532
  • 1
  • 8
  • 12
  • 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? – Nathaniel May 30 '16 at 18:01
  • 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. – Leigh May 30 '16 at 18:16
  • @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 = ''` – Tim Jasko May 31 '16 at 13:24
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>
Nathaniel
  • 21
  • 2
  • 2
    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. – Leigh May 30 '16 at 19:25