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.
Archived Comments
Whoah. What the heck is right.
I remember an IBM rep telling me once that the DB2 engine on their AS/400 was still getting updates and fixes regularly because "customers always seem to be able to write a query that throws an error". And that it one of the oldest SQL engines out there...
it=is
Does q2 also get changed?
Nope.
hah, if i had a doller for everytime Q of Q acted in a totally weird way, i'd be pretty rich by nw... i try not to use Q of Q unless i have to , prefer doing everything in SQL..
ditto goldcoastNerd. The lack of documented syntax, no joins or aliasing, and speed has made me prefer to use complex db queries rather and qoq's.
In general, I'd say it's better to write joins in a db query than doing q of q. ColdFusion's specialty isn't database processing. :-D But sometimes tables can't be joined or other scenarios exist (working with non-db data) where query of queries is so handy!
In general, I'd say it's better to write joins in a db query than doing q of q. ColdFusion's specialty isn't database processing. :-D But sometimes tables can't be joined or other scenarios exist (working with non-db data) where query of queries is so handy!
In this case, the join was the result of a SQL query and a query object built on the fly using other data.
Not to sound too absolutest (that's probably not a real word ;)) but I have a general rule about when to use a query of query. If I'm going after table data in a database I flat out will avoid using query of query like the plague. However, it does have its place when you need to perform complex querying on queryable (again - not a real word! ;)) non-table data. Query of query can totally save you when you need to query data returned from a cfdirectory call or data that you've retrieved from an exchange server. Complex file handling and e-mail data is a good place to start using query of query - just some food for thought.
I have another issue with QofQ.
I am trying to use string functions in QofQ like LEFT(string, count), or RIGHT(string, count) and I have:
" Query Of Queries syntax error.
Encountered "Left. Incorrect Select List, Incorrect select column".
For first query I am using a :
<cfdirectory directory="#application.installPath##application.PDFfilesDir#" name="memberList" action="LIST" sort = "directory ASC">
Then,
<cfquery dbtype="query" name="reportsQuery">
SELECT distinct Name, Left(Name, 12) AS PDF
FROM memberList
</cfquery>
I need to show only first 12 charachters in the <cfselect>
The simple answer is that it is not supported. Check the docs as it details which functions are supported in QoQ.
I know this is a bit old now, but with regards to Albert's comment, is there a simple way to accomplish selecting a distinct substring from a cfquery result set?
i found a pretty easy workaround for the left/right substring selection was to just do all the substring selections in the previous query that you are selecting from, alias it, and in the QoQ, you can just select whatever column you aliased in the previous query.