Query of Queries Select NULL Error

Posted At : March 14, 2008 8:00 AM | Posted By : Steve
Related Categories: ColdFusion

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 (3) |

Print |

del.icio.us |

Digg It! |

Linking Blogs

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)

[Add Comment]

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

[Add Comment]