Query of Queries Select NULL Error

I love Query of Queries. It frequently saves me a lot of time and trouble. Yesterday, however, I ran into a bug in the feature that caused a bit of heartache.

[More]

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
You can leverage CF's poor handling of the concept of NULL to your own advantage here. An empty string is often synonymous with a NULL.

So one can cast an empty string to a null timestamp, in QoQ.

Sample code (hopefully your blog will escape angle brackets...):

{code}
<cfscript>
   q1 = queryNew("iCol,sCol,dCol", "CF_SQL_INTEGER,CF_SQL_VARCHAR,CF_SQL_TIMESTAMP");
   for (i=1; i <= 10; i++){
      queryAddRow(q1);
      querySetCell(q1, "iCol", i);
      querySetCell(q1, "sCol", repeatString(chr(64+i), i));
      querySetCell(q1, "dCol", dateAdd("d", i, now()));
   }

   q2 = queryNew("iCol,sCol", "CF_SQL_INTEGER,CF_SQL_VARCHAR");
   for (i=11; i <= 20; i++){
      queryAddRow(q1);
      querySetCell(q1, "iCol", i);
      querySetCell(q1, "sCol", repeatString(chr(64+i), i));
   }

</cfscript>
<cfquery name="q3" dbtype="query">
   select   iCol, sCol, dCol
   from   q1
   union all
   select   iCol, sCol, cast('' as TIMESTAMP)
   from   q2
</cfquery>
<cfdump var="#q3#">
{code}

--
Adam
# Posted By Adam Cameron | 3/14/08 2:12 PM
Adam,

Great suggestion! A friend has actually suggested using cast, but I confidently told him that query of queries doesn't support cast (I feel sheepish now, especially since I now recall Ben Nadel having used it).

I copied your example code and it worked. When I tried to apply it to my code, however, I got a "null null" error. I should probably investigate further, but I already have a working hack in place.
# Posted By Steve Bryant | 3/17/08 6:18 AM
Has anybody seen the images that correspond to the BOOKIMAGE field?
# Posted By Phillip Senn | 7/6/09 10:05 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.