When you figure out the specs on the production server, be sure to post -
because I've never had this problem and it this is a new issue with 7 (we're
not there yet), we're going to be in for a world of hurt.
On 3/4/06, Roberto Perez [EMAIL PROTECTED] wrote:
At 11:14 AM 3/4/2006, I myself
My guess is that Region is a reserved word *somewhere* between CF, the OS,
the web server...something. That's probably why aliasing the field names fixed
the problem. In fact, I would bet that if you just aliased the region field and
grouped by it, it would STILL work on both your dev box and
I would bet that if you just
aliased the region field and grouped by it, it would STILL work on
both your dev box and the other. :0)
Mmm... no, I aliased and unaliased (if that word exists) one by one all the
columns in different combinations each time (in Group alone, in Group and
Incidentally, you can use [] to escape reserved words in query of
queries. I've had to do that on a few occasions, and I made sure when
I implemented my locale-specific query sorting function that it
escaped the column names being passed in this way since I couldn't
know that it wouldn't be used
Roberto Perez wrote:
Regarding the CFOUTPUT GROUP issue with query of queries, is it
feasible to convert the output below to cfloop's? What I have is:
(cfouptut group=region)
Region 1
(cfoutput group=city)
City1
(cfoutput)
client1
At 08:45 PM 3/3/2006, I myself wrote:
Does anyone know, off the top of their heads, if a GROUP command in
the output of a query of queries has server-specific issues?
I did more digging and this is what I found: when I do a ColumnList
on my desktop, I get a list like this:
At 10:37 AM 3/4/2006, Jochem van Dieten wrote:
Regarding the CFOUTPUT GROUP issue with query of queries, is it
feasible to convert the output below to cfloop's?
Something like:
cfset i = 1
cfoutput
cfloop condition=i LTE queryname.recordcount
Thanks, I was trying loops with the query
At 11:14 AM 3/4/2006, I myself wrote:
I tried an alias, but in the end, it would be the same thing (i.e.,
the alias would point back to the original master query) ...
Ok, it is working now. The solution was:
- use an alias for each column in the query of queries
- do not use the alias in the
It seems as if that's one area where they may be a benefit (try it and
see) - another may be if you had a large, complex query with many
joins that took a long time to execute - once that dataset is in
memory it may be quicker to extract smaller subsets via QoQ. Of course
it may not; if you use
I'm not too surprised in this case. The original query just pulls
everything from the table. The QoQ has to go through that whole
recordset and find the matching record(s), with no index to follow as
would be the case if the DB were doing it.
On 11/25/05, Billy Jamme [EMAIL PROTECTED] wrote:
So
So by that rational only unindexed and non-sequential columns, such as a text
field, is where QoQs would offer performance increases?
I'm not too surprised in this case. The original query just pulls
everything from the table. The QoQ has to go through that whole
recordset and find the matching
If your initial Query query was very complicated then using QoQ to
extract subsets would probably perform better.
-Original Message-
From: Billy Jamme [mailto:[EMAIL PROTECTED]
Sent: Friday, 25 November 2005 4:16 p.m.
To: CF-Talk
Subject: Query of Queries (QoQ) performance question
So for simple queries QoQs actually degrade performance?
If your initial Query query was very complicated then using QoQ to
extract subsets would probably perform better.
-Original Message-
From: Billy Jamme [mailto:[EMAIL PROTECTED]
Sent: Friday, 25 November 2005 4:16 p.m.
To:
QofQ doesn't support subqueries.
cheers,
barneyb
On 11/9/05, Lee [EMAIL PROTECTED] wrote:
I'm having trouble doing a subquery using a query of
queries. Is this even possible or am I doing something
wrong. I have the simplified code below. Thanks.
cfquery name=OOrder datasource=database
You can't do sub queries in Q of Q.
Ade
-Original Message-
From: Lee [mailto:[EMAIL PROTECTED]
Sent: 09 November 2005 22:15
To: CF-Talk
Subject: Query of Queries Subquery
I'm having trouble doing a subquery using a query of
queries. Is this even possible or am I doing something
Sad but true... You'll have to run a third QoQ to filter it.
..:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: Adrian Lynch [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 09, 2005 5:23 PM
To: CF-Talk
Subject: RE: Query of Queries
to allow for no records though.
Ade
-Original Message-
From: Bobby Hartsfield [mailto:[EMAIL PROTECTED]
Sent: 09 November 2005 22:28
To: CF-Talk
Subject: RE: Query of Queries Subquery
Sad but true... You'll have to run a third QoQ to filter it.
...:.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby
Is cstSpecialZip another query record set? How about...
cfquery name=Minneapolis dbtype=Query
SELECT*
FROM OOrder
Where zip IN (cfqueryparam
value=0#valueList(cstSpecialZip.zip)# cfsqltype=CF_SQL_INTEGER
list=Yes)
/cfquery
In case you're wondering, the 0 will catch empty
This is an old and annoying issue that is cause by CF trying to guess the
type of the data in the column. We've had to append a string character such
as a tilde to the data to foce everything to be a string, do the QofQ, and
then remove it before processing/display.
Hope that helps,
Brian
Thanks Brian, that worked. Ugh, what a crappy bug,
Phil
This is an old and annoying issue that is cause by CF trying to guess the
type of the data in the column. We've had to append a string character such
as a tilde to the data to foce everything to be a string, do the QofQ, and
then remove
Thanks for the idea. I still had problems though. So I added a dummy
row using querySetCell() where the offending column's value was set to
aaa (I think someone might have suggested something like this
earlier). This is a value that would never occur in real life. On
the next process that
Oh yes, and I used the cast in the other query to get QoQ to play
nicely. Thanks!
cast(OrderDetails.Order_ID AS varchar(12)) AS Order_ID,
--Jeff
On 6/23/2005 8:52 AM, Jeff Langevin wrote:
Thanks for the idea. I still had problems though. So I added a dummy
row using querySetCell() where
Would it help if I made good on my threat to write a QueryCast() UDF for CF 6.1?
On 6/23/05, Jeff Langevin [EMAIL PROTECTED] wrote:
Oh yes, and I used the cast in the other query to get QoQ to play
nicely. Thanks!
cast(OrderDetails.Order_ID AS varchar(12)) AS Order_ID,
--Jeff
On
awesome... good tip... (still a kludge though - ha)...
-Original Message-
From: Jeff Langevin [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 23, 2005 7:52 AM
To: CF-Talk
Subject: Re: Query of Queries error - solution
Thanks for the idea. I still had problems though. So I added a dummy
I have no doubt that it would be put to good use by many if you did...
--Jeff
On 6/23/2005 10:07 AM, James Holmes wrote:
Would it help if I made good on my threat to write a QueryCast() UDF for CF
6.1?
On 6/23/05, Jeff Langevin [EMAIL PROTECTED] wrote:
Oh yes, and I used the cast in the
Upgrade to CFMX 7?
I know, that is not really an answer, but this is one of the many improvements
in MX 7, the ability to type QofQ columns which did not exist in previous
versions.
Assuming this is not an option, you are going to have to do something to force
the issue, can you possible
Jeff,
One of your original queries (that pull from the DB) sees the join column as
a numeric type. re-cast it as a string type in the original query. Here's an
MS SQL example.
cast(realPartNumber AS varchar(12)) AS realPartnumber
What DB are you using?
This should cause the recordset object to
The problem is that the offending data is in the csv file which is being
turned into a query result using QuerySetCell(). That doesn't support
setting a type.
If I do the cast in the other query, QoQ throws an error saying that the
comparison of the two different data types is not
PROTECTED]
Sent: Wednesday, June 22, 2005 3:16 PM
To: CF-Talk
Subject: Re: Query of Queries error
The problem is that the offending data is in the csv file which is being
turned into a query result using QuerySetCell(). That doesn't support
setting a type.
If I do the cast in the other query
On 6/9/05, dan martin [EMAIL PROTECTED] wrote:
The work around suggested by the analyzer is to remove the keywords. Not very
helpful.
Anyone know how to do group by aggregates in MX7? It works fine in cf5.0
In case the documentation didn't come with your copy of CFMX7:
I believe MX 7 still supports aggregrates in query of queries. Check
out http://livedocs.macromedia.com/coldfusion/7/htmldocs/1266.htm
for the QoQ guide and
http://livedocs.macromedia.com/coldfusion/7/htmldocs/1272.htm for
using aggregate functions in QoQs.
Is there a particular piece of
To answer my own question for the sake of
posterity...the problem I was running into was caused
by two things: 1) In my original SELECT statement,
where I was getting data from the database, I had
repeated the sales_appointment_date field twice, 2) It
seems you can't use ODBC dates--you need to
There are all sorts of bugs like this in query of queries. This is
because CFMX is assuming the data type of each column based on the
first value in the query. So if you have a field that has bunch of
strings in it, but one row has a numeric value in that column, even if
YOU want it to be
Kind of. It's CF trying to be clever, and failing. With a QofQ,
there isn't any metadata about the column types, so CF has to attemp
to decypher it on the fly. Sometimes it works, sometimes it doesn't.
I suspect what's happening is that CF is assuming that the double
hyphen means a date, so
Of course I meant we avoid the use of Query of Queries, not cfquery. :-)
On Thu, 21 Oct 2004 13:32:08 -0400, Brian Kotek [EMAIL PROTECTED] wrote:
There are all sorts of bugs like this in query of queries. This is
because CFMX is assuming the data type of each column based on the
first value
Thanks for taking the time to post a reply to the query of queries
error. You won't belive what I found that solved that issue. First, it
was tru that MX was casting the two-hyphen varchar as a date. But no
matter what I did to solve this (using ToString, Evaluate, etc.), I could
not get
Yeah, the datatype thing is a drag. That one really bit me one day.
Did you find some of the work-arounds listed in the Macromedia live
docs? One stands out in my memory, in case you don't get anywhere with
Java - create an empty table with one column of each data type you
need in your database,
Nando, You you by any chance have link to that live doc?
Thanks
- Original Message -
From: Nando [EMAIL PROTECTED]
Date: Wed, 25 Aug 2004 03:37:41 +0200
Subject: Re: Query of Queries functionality in Java?
To: CF-Talk [EMAIL PROTECTED]
Yeah, the datatype thing is a drag. That one really
Yes, I'm the one who posted that workaround.The page is now
summarized at
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/using_r6.htm.
It is the official QofQ b*tch page!
I'm really sick of spending so much time searching for workarounds for
QofQ that gets things working until you
I've had this lovely issue come up before.Gotta love the fact that the columns are typed, but you have no control over it (AFAIK).What i had to do was insert a dummy row at the top of the query with the problematic field set as a string.This causes the column typing algorithm to set the column as
Yer, thought of that and it works, thanks.
Still very annoying though, wasted a few hours trying to track that down
this morning.
-Original Message-
From: Ryan Emerle [mailto:[EMAIL PROTECTED]
Sent: 03 December 2003 15:54
To: CF-Talk
Subject: RE: Query of Queries issue with 6.1
Just to clarify...you are saying that a QoQ sometimes changes the
datatype of a column?
btw...you should be able to rewrite the cfreturn without the eval as:
cfreturn this[arguments.InstanceName] /
it's a bit cleaner imho.
--
jon
mailto:[EMAIL PROTECTED]
Tuesday, July 1, 2003, 4:52:23 PM,
Can we see your code?
-Original Message-
From: Jose Alfonso [mailto:[EMAIL PROTECTED]
Sent: Thursday, 29 May 2003 9:09 a.m.
To: CF-Talk
Subject: Query of Queries Error
I've got a variation on this same error (Macromedia please acknowledge
this is a bug in CFMX!)
In my case I
I'm not sure, but I don't think you can do subqueries with QofQ.
Try (not tested)
Select * from Query1 where Q1MachineName not in
(#QuotedValueList(Query2.Q2MachineName)#)
UNION
Select * from Query2 where Q2MachineName not in
(#QuotedValueList(Query1.Q1MachineName)#)
-Original Message-
Rob,
Not sure if CF5 query of queries can handling your sub-selects. Anyway,
try this as a possible alternative:
Select * from Query1 where Q1MachineName not in
(#QuotedValueList(Query2.Q2MachineName)#)
UNION
Select * from Query2 where Q2MachineName not in
I am getting the same Unsupported Numeric type error on a QofQ from a site
I'm migrating from CF5 to CFMX. It worked fine in CF5. Mine sounds only
slightly different in the fact that my QofQ is only used to order the query
results. Notice I don't have all the tie fields in the QofQ, could that
What version?
Can you limit your select statement to one query that can cause the
error? That will pinpoint the exact column.
===
Raymond Camden, ColdFusion Jedi Master for Hire
Email: [EMAIL PROTECTED]
Yahoo IM : morpheus
]
Sent: Tuesday, October 01, 2002 8:40 AM
Subject: RE: Query of Queries Error
What version?
Can you limit your select statement to one query that can cause the
error? That will pinpoint the exact column.
===
Raymond Camden
PROTECTED]
Yahoo IM : morpheus
My ally is the Force, and a powerful ally it is. - Yoda
-Original Message-
From: Sam Farmer [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 01, 2002 10:00 AM
To: CF-Talk
Subject: Re: Query of Queries Error
CF MX Enterprise.
I have narrowed
PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Tuesday, October 01, 2002 10:11 AM
Subject: RE: Query of Queries Error
It sounds like you are trying to link up a non integer and integer
column from 2 queries. Can you make the added column have some default
integer values
Alex Hubner wrote:
I should have been more specific. SQL is case sensitive if
the collation
is case sensitive, which I believe to be the case for ColdFusion
(although not explicitly defined).
In that light, it is not a bug to be fixed.
Jochem, I agreee that this is not a bug, but at least
The only way around it is to make a copy of the column in your original
query, a upper(or lower) cased copy. Then do your compare against that.
In MX, we added ucase,lcase to the syntax, so you can do case
insensitive matches.
jon hall wrote:
It seems that query of query's LIKE keyword in CF5 is case sensitive.
Is there a way around this? If not, does anyone know if this is fixed
in MX?
It is not a bug to be fixed. SQL is case sensitive.
Jochem
It is not a bug to be fixed. SQL is case sensitive.
Not always. You can have case/accent-insensitive in most SQL sintax,
including data retrieve. LIKE operators and full-text searches can be
case-insensitive, why case-sensitive is mandatory in CF query of
query's?
[]'s!
Alex
| alex hübner
Friday, September 20, 2002, 5:26:27 PM, you wrote:
JvD jon hall wrote:
It seems that query of query's LIKE keyword in CF5 is case sensitive.
Is there a way around this? If not, does anyone know if this is fixed
in MX?
JvD It is not a bug to be fixed. SQL is case sensitive.
I didn't say it
Alex Hubner wrote:
It is not a bug to be fixed. SQL is case sensitive.
Not always. You can have case/accent-insensitive in most SQL sintax,
including data retrieve. LIKE operators and full-text searches can be
case-insensitive, why case-sensitive is mandatory in CF query of
query's?
I
I should have been more specific. SQL is case sensitive if
the collation
is case sensitive, which I believe to be the case for ColdFusion
(although not explicitly defined).
In that light, it is not a bug to be fixed.
Jochem, I agreee that this is not a bug, but at least a dubious
Although it is mentioned in the Query of Queries User Guide
(see below) the IN operator is not supported anymore (see
error message below)!!! Any answer from Macromedians? Who's
wrong documentation or program ?
I think you've simply got an error in your SQL.
Error Executing Database
On Monday, September 2, 2002, at 10:39 PM, Dave Watts wrote:
If those are supposed to be variable names instead of strings, you'll
need
to output them into the string of SQL that you're building:
SELECT ... IN (#MSTYLE_EDIT#,#MUSER_EDIT#,#MGRP_EDIT#)
Wouldn't they need to be enclosed in
I had the same problem. I usually solve it with aliases
cfquery name=qq dbtype=query
SELECT getdir.name as name, getdir.size as size,
getdir.datelastmodified as datelastmodified , q1.UserName as
UserName
FROM getdir, q1
WHERE getdir.name = q1.fileName
/cfquery
cfoutput
(i.e. remove the qualification altogether)
cfoutput query=qq
#DATELASTMODIFIED#,#NAME#,#SIZE#,#USERNAME#br
/cfoutput
Cheers
From: Cary Gordon [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: RE: Query of queries oddness
Date: Tue, 16 Jul 2002 15:18:20 -0700
Errr- could it be because you _reference_ the first query?
cfoutput query=qq
#GETDIR.DATELASTMODIFIED#,#GETDIR.NAME#,#GETDIR.SIZE#,#Q1.USER
NAME#br
/cfoutput
Notice you say getdir.whatever? This is the first query. You are looping
over 'qq' though.
Errr- could it be because you _reference_ the first query?
cfoutput query=qq
#GETDIR.DATELASTMODIFIED#,#GETDIR.NAME#,#GETDIR.SIZE#,#Q1.USER
NAME#br
/cfoutput
Notice you say getdir.whatever? This is the first query. You are looping
over 'qq' though.
And #Q1.UserName# should be
The code is correct. I did try your suggested changes though.
Apparently, the query of queries needs the original query identifier. You
can see this in the cfdump and also by outputting a query column list.
At 03:02 PM 7/16/2002 -0400, you wrote:
Errr- could it be because you _reference_
: RE: Query of queries oddness
Date: Tue, 16 Jul 2002 15:18:20 -0700
The code is correct. I did try your suggested changes though.
Apparently, the query of queries needs the original query identifier. You
can see this in the cfdump and also by outputting a query column list.
At 03:02 PM 7/16/2002
Three things come to mind.
First - you must specify which query that which columns fom from. You
cannot do:
SELECT ItemID,ItemNumber,ProdName, QSPriceUS,CUQty
Instead you must do:
SELECT SearchItems.ItemID AS ItemId, SearchItems.ItemNumber AS ItemNumber,
SearchItems.ProdName AS ProdName
I don't think you can really measure it... It depends not necessarily on how
many records you select, but the amount of data you are using, and how much
RAM you have.
I did a Query of a Query on a 20mb table(about 140,000 records) and CF
grabbed 20 mb of RAM for a sec, and then free'd it up.
that did not work. Same error about the value in the from clause being a
complex value.
-Original Message-
From: Brad Roberts [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 27, 2001 2:19 PM
To: CF-Talk
Subject: RE: query of queries in a custom tag
I'm taking a wild guess without trying
I'm taking a wild guess without trying it out but maybe try,
#evaluate(caller. attributes.query_name)#
-Brad
-Original Message-
From: DeWitt, Steve [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 27, 2001 5:11 PM
To: CF-Talk
Subject: query of queries in a custom tag
I am trying
101 - 169 of 169 matches
Mail list logo