Stillnet Studios

There may be something buggy here, or maybe I’m misunderstanding how this should work.

This code executes fine:

<cfquery name="qryListings" dbtype="query">
SELECT 1 as OrdBy, * FROM qryListings
UNION
SELECT 2 as OrdBy, * FROM qryListingsTmp
ORDER BY OrdBy
</cfquery>

But when I add a WHERE clause to the second part to filter out records that were in the first group:

<cfquery name="qryListings" dbtype="query">
SELECT 1 as OrdBy, * FROM qryListings
UNION
SELECT 2 as OrdBy, * FROM qryListingsTmp WHERE ref_num NOT IN
(#ValueList(qryListings.ref_num)#)
ORDER BY OrdBy
</cfquery>

I get a ColdFusion error about the number of columns being different between the two queries.

The error goes away if I list out the individual columns, instead of using an asterisk. This would not work very well in my case, since I don’t have any control over the query being returned to me and it could be changed without regard to this code. Thankfully I’m on CF8 and was able to dynamically grab the column names.

<cfquery name="qryListings" dbtype="query">
SELECT 1 as OrdBy, #ArrayToList(qryListingsTmp.GetColumnNames())# FROM qryListings
UNION
SELECT 2 as OrdBy, #ArrayToList(qryListingsTmp.GetColumnNames())# FROM qryListingsTmp WHERE ref_num NOT IN
(#ValueList(qryListings.ref_num)#)
ORDER BY OrdBy
</cfquery>