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.
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.
Pluralizing in ColdFusion
Steve Bryant said: James,
Sorry I missed your comment earlier. I haven't compared to other libraries. I didn't even kn...
[More]
Pluralizing in ColdFusion
James Moberg said: Have you compared the results with other libraries? I use an inflector CFC and I see that some of t...
[More]
Git Branching Strategy for Web Development
Thomas said: Hi Steve,
thanks a lot for this post. While considering Git Web Flow for our branching strategy, t...
[More]
Getting Around Windows 7 "Destination Path Too Long" Error When Deleting Files
MxP said: The above robocopy command worked like a charm! I had a deeply nested folder structure that I wante...
[More]
New Open Source ColdFusion Shopping Cart
Paul said: I gave up on this after all kinds of pathing issues. Couldn't be bothered tracing where to change th...
[More]
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
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.