ColdFusion Muse

Query of a Query Quirkiness

Mark Kruger March 12, 2005 3:29 PM Coldfusion Troubleshooting Comments (0)

Query of a query was introduced in CF 5 and has been a great benefit to Coldfusin developers. The concept is simple and unique to Coldfusion. I have yet to find anything like it native to any other language. Using Q of a Q a developer can pull a larger dataset from the database and chop it into smaller subsets of data. This is espacially handy for reporting. Query of a query has a number of quirks however. Here's one that bit me.

Let's say you have the following query:

<cfquery name="getData" datasource="#ds#"
   SELECT    background, bgcolor, Cat_id
   FROM    bulletins
   WHERE   type = 'mustard'
</cfquery>
A column in the query calls for cat_id. Let's suppose that Cat_id is foreign key for your category table where you hold titles (labels) for various categories. One of the things you might want to do is to get a list of all the category ids returned by the query. Using Q of a Q this is easy. It would look like this:
<cfquery name=&quot;getDistinct&quot; dbtype="query">
   SELECT    DISTINCT cat_id
   FROM    getdata
</cfquery>

Now lets throw in a wrinkle. Let's say you have some bulletins that are "global" and do not belong to any category. Those bulletins get returned by the initial query as zero length strings. That means if you wanted to get all the rows with a cat_id of 10 you would have to use "WHERE Cat_id = '10' " (notice the single quotes). Q of a Q will treat the column like a string because it is not bound to anything.

It get's worse. Because some of the columns return an empty length string, your "distinct" clause will create a row with an empty string in it. If you are looping through the list to get categories from - say another query or from the database, you will likely generate an error. You might think that this would be readily visible using the <CFDUMP> tag, but this is not the case. <CFDUMP> hides that empty string and doesn't give you an idea that it's there. Here's an example (look closely!):
TEMPINT
1
2
5
4
88
8
65

And here's an example with a little comment added to that empty row
TEMPINT
1
2
here I am!
5
4
88
8
65

To fix it you have several options. One course of action might be better database planning. Consider using a default in that column for example. Another course of action is to remake that Q of a Q to qualify the column like so:

<cfquery name=&quot;getDistinct&quot; dbtype="query">
   SELECT    DISTINCT cat_id
   FROM    getdata
   WHERE   cat_id &lt;&gt; ''
</cfquery>

However you choose to do it make sure you test it with various recordsets before you deploy it. That goes for all Q of a Q code - this is certainly not the only quirk.

  • Share:

0 Comments

Leave this field empty

Write a comment

If you subscribe, any new posts to this thread will be sent to your email address.