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 needed to join two queries with a UNION. The first query had a date field that I needed to use. In order to get the UNION to work, I needed to include the same column in the second query. In that query, however, I had no date.
I run into this scenario from time to time in UNION queries with a database and it is easy to solve. I just write NULL AS ColumnName and continue about my business.
With Query of Queries, however, that didn't work. It returned an error stating:
Encountered "NULL. Incorrect Select List, Incorrect select column,
Apparently ColdFusion was looking for a column named "NULL" instead of treating it as a SQL keyword for the NULL value.
I wrote some code to reproduce the problem with the cfbookclub datasource that comes with ColdFusion:
SELECT *
FROM books
</cfquery>
<cfquery name="qBooks" dbtype="query">
SELECT BookID,Title,Genre,BookImage
FROM qBooks
WHERE Genre = 'Fiction'
UNION
SELECT BookID,Title,Genre,NULL AS BookImage
FROM qBooks
WHERE Genre = 'Non-fiction'
</cfquery>
In this example, I could have replaced NULL with '' and that would have fixed the problem. When I try to do that with a date, however, I get an error about a datatype mismatch.
I was able to hack around the problem, but I wish I didn't have to do so.
So, if you ever see this error, that is the problem.
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.