About Query of Queries

After you have created a record set with a tag or function, you can query the record set in several dependent queries. A query that retrieves data from a record set is called a Query of Queries. A typical use of a Query of Queries is to retrieve an entire table into memory with one query, and then access the table data (the record set) with subsequent sorting or filtering queries. In essence, you query the record set as if it were a database table.

Note: Because you can generate a record set in ways other than using the cfquery tag, the term In Memory Query is sometimes used instead of Query of Queries.

Comments are no longer accepted for ColdFusion MX 6.1. ColdFusion 8 is the current version.

Comments

jrunrandy said on Jul 28, 2004 at 4:21 AM :

Query of Queries feedback summary
Because of the large number of comments that this topic has elicited, we are consolidating similar descriptions into this comment, along with a response for each.

Overview: Prior to ColdFusion MX, columns in query of queries were typeless. However, ColdFusion MX uses a more JDBC-like behavior and has the concept of data types in query columns. This change in behavior may require changes to CF 5 applications, particularly for manually constructed queries. Additionally, some of the side-effects of this change have been been classified as bugs and will be fixed in the next release of ColdFusion MX.

Please continue to add documentation-related comments to this page. However, if your comment relates to undesired behavior in the ColdFusion MX query of queries functionality, please do not add comments to this page. Instead, use one of the following alternatives:
* If you want to discuss the query of queries feature and possible workarounds with other members of the ColdFusion community, use the online forums: http://webforums.macromedia.com/coldfusion
* If query of queries behavior is seriously affecting your business, you should request Technical Support by calling the Macromedia Call Center at 1-800-945-9120 and opening a paid support incident. If your issue is found to be a bug, there is no charge for the incident.

Randy Nielsen
ColdFusion Documentation Manager
Macromedia, Inc.

Date: 9/22/03 By: TheoPeters
Summary: This developer noted that there is no way to tell QueryNew what the datatype of each column is, so if your data has any string values that look like a number or a date you will get an error.
Status: Fixed in next release of ColdFusion.
Note: This comment is duplicated by the following comments:
* 1/21/04 by no screen name
* 3/18/04 by Jens
* 3/23/04 by clomvardias
* 6/25/04 by no screen name
* 7/8/04 by brent.nicholas
* 7/23/04 by Bob Sacamano
* 7/24/04 by KkkMaxxX
* 7/25/04 by igneous
******
Date: 10/2/2003 By no screen name
Summary: This developer ran into a situation where a null value was returned and CF threw a [cannot convert the value "" to a boolean] error.
Status: To fix this, modify the QofQ to include "and foo is not null" in the WHERE clause as explained on http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/using_35.htm
******
Date: 12/17/2003 By: HolgerL
Summary: This developer had a situation in which, in a UNION, ColdFusion seemed to be casting the same field types to different data types.
Status: We have been unable to duplicate this behavior.
******
Date: 1/2/2004 By: jsevlie
Summary: This developer suggested a workaround that hard-wires all fields to strings:
<cfset x = 5>

<cfset QuerySetCell(qDatabase, "sales", "#x#", row_counter)>

instead of

<cfset x = 5>
<cfset QuerySetCell(qDatabase, "sales", x, row_counter)>
******
Date: 1/2/2004 By: jwcb
Summary: This developer had a question with regard to the CF versions that support QofQ.
Status: HalL responded by saying the QofQ is supported in ColdFusion 5, ColdFusion MX, and ColdFusion MX 6.1 noting that the SQL syntax support was enhanced between 5.0 and MX, but all three releases support basic features.
******
Date: 1/16/2004 By: Gary M G
Summary: This developer noted that in some cases, ColdFusion thought an Int was a BigInt.
Status: He was able to solve this by using a SQL Cast function to convert the Int to a BigInt: Cast(mydata as BigInt).
******
Date: 2/12/04 By: Brian428
Summary: This developer described a situation in which QofQ aggressively treats numeric data (in the format xxx-xx-xxxx) as a date.
Status: HalL entered this as bug 54252 and it is fixed in the next release of ColdFusion.
******
Date: 2/25/04 By: mindtrap
Summary: This developer described a situation when using QofQ where one of the columns was a date and the recordset had come from an Oracle database and CF returned an invalid type conversion error.
Status: On ColdFusion MX only, using cfqueryparam within the QoQ resolved the situation. It is still the behavior in ColdFusion 5.
******
Date: 3/16/04 By: Funke
Summary: This developer described a workaround when creating a UNION of three queries. The type mismatch only occurred when one of the statements returned 0 rows. The workaround is to check for recordcount=0 before creating the QofQ.
******
Date: 4/2/2004 By: no screen name
Summary: This developer described a workaround by creating a QueryOfQuery table in the database. The table has a Char field, a Number field, and a Date field. Then query this empty table to return an empty query with the column names and types: SELECT StringField as IndividualID, StringField as Email, DateField as CommitDate FROM QueryOfQuery WHERE 1 = 0
******
Date: 6/18/2004 By: GAlanShepard
Summary: This developer described a situation related to the case-sensitivity of QofQ. Status: Query of queries is case-sensitive, as described on http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/using_27.htm
******
Again, please continue to add documentation-related comments to this page. However, if your comment relates to undesired behavior in the ColdFusion MX query of queries functionality, please do not add comments to this page. Instead, use one of the alternatives listed at the top of this comment.

No screen name said on Sep 2, 2004 at 9:07 AM :

I had a similar problem where a string of the form "11000000001010?10010000" caused an attempt to convert it to double. The query was built using QuerySetCell() in the usual fashion and the CFDUMP showed the contents as advertised. However, the subsequent QofQ bombed. I wanted ALL the columns to be strings in terms of data type. My workaround was to make sure that the FIRST record of my hand built query had data that forced the column type to string i.e.

<cfset querysetcell(myQ,"column1","xxxxxxx")>
<cfset querysetcell(myQ,"column2","xxxxxxx")> etc.

Apparently the FIRST record sets the column type. Then at the end I effectively removed my "dummy" record thus:

<cfquery name="myQ2" dbtype="query">
select * from myQ where column1 != 'xxxxxxx'
</cfquery>

I think that QueryNew(columnlist) should be modified to accept an optional second parameter (for backwards code compatibility) thus:

QueryNew(columnlist [,columnTypeList])

which would allow passing in not only the column names but also corresponding column type specificers.

bjquinn said on Sep 9, 2004 at 9:53 AM :

I am upset with the way that Macromedia is dealing with this issue. I think the very fact that someone at Macromedia had to consolidate dozens of complaints about the query of queries problem to clean up the documentation page and to ask us to please quit complaining about its non-functionality on this page (there's always the forums or you can pay Macromedia to fix it!) - that should prove to them how annoying this issue is. The fact that they refuse to fix it until the next version of Cold Fusion (no hotfix?!) is rediculous. I've been having more and more of these types of issues lately, including one that I've been dealing with Macromedia tech support with on ALL YEAR - they need to realize they can't treat major problems this way or we're all eventually going to go somewhere else. Macromedia is trying to treat us like Microsoft does, but unfortunately for Macromedia, THEY have competition. Go PHP.

jrunrandy said on Sep 9, 2004 at 10:53 AM :

bjquinn: I can understand that you're frustrated with Query of queries functionality. However, I hope that you'll understand that when you deal with LiveDocs, you're dealing with documentation; not development, not product marketing, not support. You can flame me all you want, but I'm still a Tech Writer, not a developer. Believe me when I tell you that I have lobbied development on your behalf. The Query of Queries enhancements in the next release of ColdFusion (Blackstone) have tried to address most of the comments made on this page.

I suggest signing up for the Blackstone Beta to ensure that the Query of Queries fixes/enhancements meet your needs. You can sign up from http://www.macromedia.com/support/programs/beta.html. Comments and complaints made to the Blackstone Beta forum will get a lot more attention than anything you post to this page.

I will leave your comment in the page as a show of good faith. However, if this page once again gets to the point that it's not functioning as documentation, I'll consolidate the comments again.

No screen name said on Jan 19, 2005 at 6:12 AM :

Preventing the posting in LiveDocs of work-arounds to bugs that have no fix available is in my opinion inappropriate. Telling me to not only find out about an issue in LiveDocs, but also go find the work around info in "forums" is unhelpful to me and likely to waste hours and more likely days. Refactoring and condensing the work arounds as the tech writer did here is very helpful - however, whatever fix or work around Tech support supposedly would help me enact "for free" unless they decide to call the bugs "enhanced functionality of MX" should be described here in Live Docs. Thank you to MM for employing the writer to condense the comments for us. When mentioning joining Blackstone beta, a link would be helpful. Getting lost in the MM site is quite easy and unproductive for most programmers.

jrunrandy said on Jan 19, 2005 at 10:14 AM :

I probably shouldn't have said "and possible workarounds..." in my comment. Clearly, that's the kind of stuff we want on LiveDocs pages.

The point I was trying to make was that the Forums get way more traffic than LiveDocs, so you're more apt to get a faster response by posting questions to the forums. However, we just added a feature to LiveDocs that allows you to get e-mail notification when a new comment is added to a page, so maybe LiveDocs traffic will increase.

Also, have you tried the new Knowledge Base feature on the Support Center? http://www.macromedia.com/support/coldfusion I've found it to be pretty helpful.

Kurt Eye said on Jan 26, 2005 at 11:36 AM :

I've found that the ToStr function fixes the datatyping issue for me, thusly:

temp = querysetcell(entry, 'programnumber', tostr(your_variable)));

...where "your_variable" is a value that is generally numeric but
occassionally has an alphanumeric value. With this solution I'm no
longer getting a "Can't convert string to java type LONG" error in my
Query of Queries when an alphanumeric crops up.

Kurt Eye said on Jan 26, 2005 at 12:13 PM :

I take it back. That doesn't work either.

sanaullah said on Feb 18, 2005 at 2:29 AM :

I think there is a bug in query of query if we try this

select top 20 cms_id,cms_name from application.contents

the error comes up that [ from keyword is missing from select statment]

prayank said on Feb 24, 2005 at 3:20 AM :

sanaullah could you please tell me which version of CF are you using?

jrunrandy said on Feb 24, 2005 at 6:36 AM :

What version are you moving from? I talked to a developer and he said CF hasn't supported "top" since the base CFMX release, but I can't find any documentation anywhere that says CF ever supported this keyword.

In any case, I'm sorry to say that CFMX 7 does not support "top."

cardinalweb said on Mar 31, 2005 at 11:58 AM :

just use the MaxRows attribute of the CFQuery tag instead of doing Top.

Murrah said on Nov 22, 2005 at 11:08 PM :

Re: dates in Query of Query. Querying an MS_Access db then querying the results.

Using <cfqueryparam value="#OfferArriveDate#" cfsqltype="CF_SQL_DATE"> in the QoQ still gave "unsupported type conversion error".

The following worked:

MS-Access query:

<CFQUERY name="BookedEnquiries" dataSource="#request.DSN#">
SELECT rmID, BookingDate, Format(ArriveDate) as ArrDate, Format(DepartDate) as DepDate
FROM RoomBookings
WHERE enqID = #url.enqID# and Status = 1
</CFQUERY>

QoQ:
<cfquery dbtype="query" name="bookedChk">
Select BookingDate from BookedEnquiries
WHERE rmID = #rmID# and ArrDate = '#DateFormat(offerArriveDate,"mm/dd/yyyy")#' and DepDate = '#DateFormat(offerDepartDate,"mm/dd/yyyy")#'
</cfquery>

I assume using Format() in the original sql and single quotes in the QoQ means we are comparing string to string. If you needed to check for date ranges you may need to convert the original dates to some other format (eg yyyymmdd) and compare to that. I didnt need that so I havent tried it.

Murrah said on Nov 23, 2005 at 6:21 PM :

Correction to my last post. The DateFormat should use "m/d/yyyy" not "mm/dd/yyyy".

ie:
WHERE rmID = #rmID# and ArrDate = '#DateFormat(offerArriveDate,"m/d/yyyy")#' and DepDate = '#DateFormat(offerDepartDate,"m/d/yyyy")#'

No screen name said on Oct 19, 2006 at 12:49 PM :

Moments after my last post I discovered a solution. I tested it and it works great! I wrote a function to replace the QueryNew function. Feel free to use and modify to your needs:

<CFFUNCTION name="queryNewQuery" returnType="query" access="public">

<CFARGUMENT name = "queryColumnList" required = "yes">
<CFARGUMENT name = "queryTypeList" required = "yes">


<CFSET VAR num = 100>
<CFSET VAR dec = 1.1>
<CFSET VAR bit = TRUE>
<CFSET VAR str = "xxx">


<CFSET VAR i = "">
<CFSET VAR myQuery = QueryNew(queryColumnList)>
<CFSET VAR qDefineTypes = "">


<CFSET QueryAddRow(myQuery,1)>
<CFLOOP from = 1 to = "#ListLen(queryColumnList)#" index="i">
<CFSET QuerySetCell(myQuery,ListGetAt(queryColumnList,i),evaluate(ListGetAt(queryTypeList,i)),1)>
</CFLOOP>


<CFQUERY name="qDefineTypes" dbType="query">
SELECT *
FROM myQuery
WHERE 1 <> 1
</CFQUERY>


<CFRETURN qDefineTypes>
</CFFUNCTION>


Contents > Developing ColdFusion MX Applications > Using Query of Queries > About Query of Queries