A user reported this to me earlier in the week. I was sure he was wrong until I confirmed it myself. Imagine you have 2 queries you want to join using a query of query. Here is a quick sample.
<cfquery name="q" datasource="blogdev" maxrows="5"> select id, title,posted from tblblogentries order by posted desc </cfquery> <cfquery name="q2" datasource="blogdev" maxrows="5"> select id, title,posted from tblblogentries order by posted asc </cfquery>
Admittedly, this is kind of a dumb example, but I wanted to keep it simple. q is a query sorted by posted, descending, and q2 is the reverse of that. To join with query of queries, you must use a where clause, you can't use join. Here is the QofQ I used:
<cfquery name="z" dbtype="query"> select q.posted, q.id, q.title, q2.id as qid from q,q2 where q.id = q2.id order by q.id asc </cfquery>
Note that I gave the new query the name z. Everything should be kosher, right? Well watch what happens when I dump q before and after the query of query.
What the heck, right? The error goes away if you make a duplicate of q and use that in the query of query.