Re: Query of Queries Error
I figured it out. The last column had some NULL values which I guess it didn't like even though other columns have NULLs and it's fine (Sounds like a bug). I defaulted the NULL ones to "N/A" so it works now. Also had to declare all my columns as VARCHARs in my QueryNew set statement. >try to dump query "docQry" chk if it is returning any row. > > >> ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352769 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of Queries Error
in select statement try to select one column at a time to chk which column's data is causing the error. On Wed, Sep 26, 2012 at 7:12 PM, Chad Baloga wrote: > > Yes it is, I have been playing with it and dumping results to see what > data may be causing the error > > >try to dump query "docQry" chk if it is returning any row. > > > > > >> > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352768 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of Queries Error
Yes it is, I have been playing with it and dumping results to see what data may be causing the error >try to dump query "docQry" chk if it is returning any row. > > >> ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352767 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of Queries Error
try to dump query "docQry" chk if it is returning any row. On Wed, Sep 26, 2012 at 6:50 PM, Chad Baloga wrote: > > I am try to run a query of queries to order a query I create on the fly. > On DEV I do not get an error. On PROD I am getting: > > "The system has attempted to use an undefined value, which usually > indicates a programming error, either in your code or some system code. > > Null Pointers are another name for undefined values." > > My Code: > >SELECT * FROM docQry ORDER BY sysModule > > > If I take the ORDER BY out, I still get the error. > > Any suggestions? > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352766 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of Queries and GROUP - use CFLOOP?
use a cfoutput with group attribute instead? #GetStates.StateName# #GetStates.StateID# - #GetStates.StateRegion# #GetStates.RegionID# #GetStates.PropertyLocationName# Azadi On 03/08/2011 09:59 , Chris Sizemore wrote: > I am using a cfloop condition approach to perform a standard branched tree > operation. I have four tables and my query is pretty straight forward. The > output is not working though. It is returning the correct amount of results > and is organizing them properly but the values displayed are repeating. I've > looked over it again and again. It returns the proper fields and order in > MSSQL Server Management Studio Express... Any ideas? > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346473 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of Queries and GROUP - use CFLOOP?
I am using a cfloop condition approach to perform a standard branched tree operation. I have four tables and my query is pretty straight forward. The output is not working though. It is returning the correct amount of results and is organizing them properly but the values displayed are repeating. I've looked over it again and again. It returns the proper fields and order in MSSQL Server Management Studio Express... Any ideas? Here's my query SELECT FFState.StateID, FFState.StateName, FFStateRegion.RegionID, FFStateRegion.StateID AS Expr1, FFStateRegion.StateRegion, FFRegionCounty.CountyID, FFRegionCounty.RegionID AS Expr2, FFRegionCounty.StateID AS Expr3, FFRegionCounty.LocationName, FFPropertyLocation.PropertyLocationID, FFPropertyLocation.RegionCountyID, FFPropertyLocation.RegionStateRegionID, FFPropertyLocation.RegionStateID, FFPropertyLocation.PropertyLocationName FROM FFState INNER JOIN FFStateRegion ON FFState.StateID = FFStateRegion.StateID INNER JOIN FFRegionCounty ON FFStateRegion.RegionID = FFRegionCounty.RegionID INNER JOIN FFPropertyLocation ON FFRegionCounty.CountyID = FFPropertyLocation.RegionCountyID ORDER BY FFState.StateName ASC, FFStateRegion.RegionID ASC, FFRegionCounty.CountyID ASC, FFPropertyLocation.PropertyLocationName ASC ( I know the syntax as posted may not be preserved but it is in dreamweaver. ) Here's the output as I got it from another post. I did modify this a bit to fit the query and four loops instead of three loops. #GetStates.StateName# #GetStates.StateID# - #GetStates.StateRegion# #GetStates.RegionID# #GetStates.LocationName# #GetStates.PropertyLocationID# #GetStates.PropertyLocationName# ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346470 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of Queries and GROUP - use CFLOOP?
I am using a cfloop condition approach to perform a standard branched tree operation. I have four tables and my query is pretty straight forward. The output is not working though. It is returning the correct amount of results and is organizing them properly but the values displayed are repeating. I've looked over it again and again. It returns the proper fields and order in MSSQL Server Management Studio Express... Any ideas? Here's my query SELECT FFState.StateID, FFState.StateName, FFStateRegion.RegionID, FFStateRegion.StateID AS Expr1, FFStateRegion.StateRegion, FFRegionCounty.CountyID, FFRegionCounty.RegionID AS Expr2, FFRegionCounty.StateID AS Expr3, FFRegionCounty.LocationName, FFPropertyLocation.PropertyLocationID, FFPropertyLocation.RegionCountyID, FFPropertyLocation.RegionStateRegionID, FFPropertyLocation.RegionStateID, FFPropertyLocation.PropertyLocationName FROM FFState INNER JOIN FFStateRegion ON FFState.StateID = FFStateRegion.StateID INNER JOIN FFRegionCounty ON FFStateRegion.RegionID = FFRegionCounty.RegionID INNER JOIN FFPropertyLocation ON FFRegionCounty.CountyID = FFPropertyLocation.RegionCountyID ORDER BY FFState.StateName ASC, FFStateRegion.RegionID ASC, FFRegionCounty.CountyID ASC, FFPropertyLocation.PropertyLocationName ASC ( I know the syntax as posted may not be preserved but it is in dreamweaver. ) Here's the output as I got it from another post. I did modify this a bit to fit the query and four loops instead of three loops. #GetStates.StateName# #GetStates.StateID# - #GetStates.StateRegion# #GetStates.RegionID# #GetStates.LocationName# #GetStates.PropertyLocationID# #GetStates.PropertyLocationName# ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346469 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Query of Queries Empty String Issue
NULLs are actually NULL now (what a concept, right?). If possible, I'd filter the nulls out on the main (real) query... if not, I'd probably use this instead: AND (order is NULL or order = '') .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Donnie Carvajal [mailto:donnie.carva...@transformyx.com] Sent: Tuesday, August 02, 2011 11:32 AM To: cf-talk Subject: Query of Queries Empty String Issue I have the following query of queries query SELECT code, title FROM Nav WHERE nav = 'Calendar' AND order = '' It has recently stopped returning results when upgrading to CF9. If I change the where clause to the following it returns the record WHERE nav = 'Calendar' AND order IS NULL Any ideas why the original where clause stopped working? I thought CF treated all NULL value fields in a record set as an empty string. Donnie ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346467 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of Queries Empty String Issue
>>Any ideas why the original where clause stopped working? I thought CF >>treated all NULL value fields in a record set as an empty string. probably the CF9 code is more consistent with SQL. Right, if a field is returned NULL in a query, CF treats it as an empty string, but apparently, the NULL state of the column is now preserved and transmitted to QoQ, which is logical after all. I think that column types are also preserved now. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346459 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
> I have a query which returns the result set like below. The column names are > like 1990, 1991, 1992 and there are certain amount > for each year > > 1990 1991 1992 > 1000 5000 6000 > 2000 2000 3000 This doesn't address your question directly, but again this is a very poor database design. I strongly recommend that you learn how to design a normalized database schema. If you design your schema well, you won't run into the problems you're describing. Of course, if you're not responsible for the database design, or if you're building a query that presents a denormalized recordset from a normalized schema, feel free to disregard this. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334432 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
>Ah, right QoQ can be bitchy. Can't you alias the column names in the initial >query? > >SELECT my1990 = [1990], my1991 = [1991] >or >SELECT [1990] as my1990 etc etc > >That way can reference them without any issues. > >P.S. 1990 and the like are absolutely horrible column names. > >On Wed, Jun 9, 2010 at 11:41 AM, fun and learning >wrote: > >> Thanks you all for the replies. Prepending a variable before the year helped me ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334426 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
If you can't rename the columns as someone else suggested, you can change your select to select 1990 as y1990, 1991 as y1991, etc.. then in your query of querys you do select sum(y1990) as sum1990 On Wed, Jun 9, 2010 at 8:27 AM, fun and learning wrote: > > HI All - > > I have a query which returns the result set like below. The column names are > like 1990, 1991, 1992 and there are certain amount for each year > > 1990 1991 1992 > 1000 5000 6000 > 2000 2000 3000 > > I have a query of query which finds the sum of the amounts for year 1990, and > I use the query like Select SUM(1990), it returns the sum of 1990+1990. How > to make sure that 1990 is recognized as a column instead as a number. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334423 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
Ah, right QoQ can be bitchy. Can't you alias the column names in the initial
query?
SELECT my1990 = [1990], my1991 = [1991]
or
SELECT [1990] as my1990 etc etc
That way can reference them without any issues.
P.S. 1990 and the like are absolutely horrible column names.
On Wed, Jun 9, 2010 at 11:41 AM, fun and learning
wrote:
>
> >Your first issue is that that is not a good db practice naming columns as
> >numbers. If you put a prefix of "y" in front of the column name in your
> >table (or whatever you chose, i.e y1990, y1991, y1992) it wouldn't have
> this
> >issue, it would force your SQL to actual look for a table column rather
> than
> >just summing the number you entered. In some databases you can force the
> >issue by surrounding the column header in quotations (Sum('1990')) but you
> >cannot count on that, you are better off renaming your column headers as I
> >said earlier.
> >
> >Jim Eisenhauer
> >
> >
> >On Wed, Jun 9, 2010 at 8:27 AM, fun and learning >wrote:
> >
> >>
>
> I get the following error when I use quotes
> Query Of Queries syntax error.
> Encountered "SUM. Lexical error at line 0, column 0. Encountered: "\""
> (34), after : "
>
> and the following error when I use brackets
> Query Of Queries syntax error.
> Encountered "SUM ( [. Incorrect Select List
>
>
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334414
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
>Your first issue is that that is not a good db practice naming columns as
>numbers. If you put a prefix of "y" in front of the column name in your
>table (or whatever you chose, i.e y1990, y1991, y1992) it wouldn't have this
>issue, it would force your SQL to actual look for a table column rather than
>just summing the number you entered. In some databases you can force the
>issue by surrounding the column header in quotations (Sum('1990')) but you
>cannot count on that, you are better off renaming your column headers as I
>said earlier.
>
>Jim Eisenhauer
>
>
>On Wed, Jun 9, 2010 at 8:27 AM, fun and learning
>wrote:
>
>>
I get the following error when I use quotes
Query Of Queries syntax error.
Encountered "SUM. Lexical error at line 0, column 0. Encountered: "\"" (34),
after : "
and the following error when I use brackets
Query Of Queries syntax error.
Encountered "SUM ( [. Incorrect Select List
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334413
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
Your first issue is that that is not a good db practice naming columns as
numbers. If you put a prefix of "y" in front of the column name in your
table (or whatever you chose, i.e y1990, y1991, y1992) it wouldn't have this
issue, it would force your SQL to actual look for a table column rather than
just summing the number you entered. In some databases you can force the
issue by surrounding the column header in quotations (Sum('1990')) but you
cannot count on that, you are better off renaming your column headers as I
said earlier.
Jim Eisenhauer
On Wed, Jun 9, 2010 at 8:27 AM, fun and learning wrote:
>
> HI All -
>
> I have a query which returns the result set like below. The column names
> are like 1990, 1991, 1992 and there are certain amount for each year
>
> 1990 1991 1992
> 1000 5000 6000
> 2000 2000 3000
>
> I have a query of query which finds the sum of the amounts for year 1990,
> and I use the query like Select SUM(1990), it returns the sum of 1990+1990.
> How to make sure that 1990 is recognized as a column instead as a number.
>
> Thanks
>
>
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334411
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
SUM([1990]) Just use square brackets around column name. On Wed, Jun 9, 2010 at 11:27 AM, fun and learning wrote: > > HI All - > > I have a query which returns the result set like below. The column names > are like 1990, 1991, 1992 and there are certain amount for each year > > 1990 1991 1992 > 1000 5000 6000 > 2000 2000 3000 > > I have a query of query which finds the sum of the amounts for year 1990, > and I use the query like Select SUM(1990), it returns the sum of 1990+1990. > How to make sure that 1990 is recognized as a column instead as a number. > > Thanks > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334410 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
Should be double quotes I think
Select SUM("1990")
Some DBs use brackets too I think
sum([1990])
Not sure how it would work undr the hood with query of query, can't you
pull this from the db directly?
On Wed, 2010-06-09 at 11:27 -0400, fun and learning wrote:
> HI All -
>
> I have a query which returns the result set like below. The column names are
> like 1990, 1991, 1992 and there are certain amount for each year
>
> 1990 1991 1992
> 1000 5000 6000
> 2000 2000 3000
>
> I have a query of query which finds the sum of the amounts for year 1990, and
> I use the query like Select SUM(1990), it returns the sum of 1990+1990. How
> to make sure that 1990 is recognized as a column instead as a number.
>
> Thanks
>
>
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334409
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of Queries Functions
Cool. The help is really appreciated. What a great resource. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319863 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries Functions
Although you can't DELETE a record from a QoQ, you can SELECT all other records and save them into the same QoQ variable. In other words, instead of removing the unwanted records, just select all of the wanted records. Thanks, Mike -Original Message- From: Ron Gruner [mailto:webmas...@gruner.com] Sent: Thursday, February 26, 2009 8:23 AM To: cf-talk Subject: Re: Query of Queries Functions There's no obvious way to delete a QofQ row. Am I missing something? Thanks again. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319862 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries Functions
There's no obvious way to delete a QofQ row. Am I missing something? Thanks again. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319861 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries Functions
On Thu, Feb 26, 2009 at 6:35 AM, Ron Gruner wrote: > > Thanks Matt. Is there some other way to build a small and temporary database > that resides in RAM and can be manipulated with all, or most, SQL functions? > I suppose Structures is the best approach but they're new for me. > Yes, you could do structures or an array of structures. If temporary is all you need and not actual database persistence, you can work with the query result set also. You can manipulate data in that with QuerySetCell. You can also add rows and columsn with QueryAddRow and QueryAddColumn. Check the docs for actual usage and examples. -- Matt Williams "It's the question that drives us ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319859 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries Functions
Thanks Matt. Is there some other way to build a small and temporary database that resides in RAM and can be manipulated with all, or most, SQL functions? I suppose Structures is the best approach but they're new for me. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319858 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries Functions
On Thu, Feb 26, 2009 at 5:56 AM, Ron Gruner wrote: > > Is there a way to do SQL Updates and Deletes on record sets organized as > Query of Queries? It seems that only SELECT works. I'm using CF8. > A query of queries does not make a connection to the actual database. It is only a way to further filter previously retrieved record sets. So no, you cannot do updates or deletes. -- Matt Williams "It's the question that drives us ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319857 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries error
i had a similar problem and turned out to be that one of the values in the
order by clause was empty - cf seems to mistake it for a empty string and
therefore was producing the same error you are experiencing... check all values
in the problem column to ensure there are no empty values
i also get around alot of query of query problems by using cast()
hope this helps
> I have a runtime query of a query error as below.
> java.lang.String cannot be cast to java.util.Date
> But the output format is correct and is in date. This application was
> developed in coldfusion 6.1 and sqlserver2000. But now we get this
> error when it was migrated to coldfusion 8 and sql server 2005.
> Below is the query block. There error comes in the select statement.
> if (NOT IsDefined("URL.sOrderBy")) URL.sOrderBy = "dtStartDate";
> if (NOT IsDefined("URL.sOrderType")) URL.sOrderType = "DESC";
>
>
>
>
> SELECT *
> FROMqCombinedProjects
> ORDER BY #URL.sOrderBy# #URL.sOrderType#;
>
>
>
>
> SELECT *
> FROMqCombinedProjects
> ORDER BY dtStartDate DESC;
>
>
>
>
> dtstartdate is in date format. Please help as this really urgent
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312097
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries
Thanks! I just realised that I could do that. Now I have to figure out whether it is a good idea to store 6 rows in an application scope query. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311940 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries
You can keep it in a query, but you will need to build the query yourself in the cfloop with querynew() queryaddrow() and querysetcell() etc. (I think I spelled those right.) It is MUCH more verbose then a qofq, but will accomplish the same thing. ~Brad Original Message Subject: Re: Query of Queries From: "Craigsell" <[EMAIL PROTECTED]> Date: Tue, September 02, 2008 12:24 pm To: CF-Talk Dang. I was trying to keep this in a query datatype so I could use it in subsequent QoQ. I wanted to get rid of the names I don't need to reduce memory usage -- I have a lot of image names to store! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311937 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries
Dang. I was trying to keep this in a query datatype so I could use it in subsequent QoQ. I wanted to get rid of the names I don't need to reduce memory usage -- I have a lot of image names to store! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311917 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries
I know qofq doesn't do substring. I don't think it does any string manipulation like replace. You will probably need to loop over the query and do it manually or do it in the database. > Original Message > Subject: Query of Queries > From: "Craigsell" <[EMAIL PROTECTED]> > Date: Tue, September 02, 2008 11:45 am > To: CF-Talk > > I'm trying to use functions in a QoQ to parse out some text. I have a > directory that has folders of images in it. What I want is a simple QoQ that > will extract the directory and filename from a CFDIRECTORY tag into another > query. My plan is to put this resulting query into the application scope > since this data is relatively static and time consuming to generate. > > >directory ="#ThePath#" > action="list" > name="ImageList" > filter="*.jpg" > recurse = "yes" > type="all"> > > > SELECT name as imageName, replace(directory,"#ThePath#","") as imageDir > FROM ImageList > > > myList would be set as an application scope variable in the next stages of > this. I'll use it in another QoQ. > > Thanks! > > Warren Koch > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311915 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: query of queries or something else
>How about something like this: > > >SELECT f.AssetID, f.name, i.text, i.amount >FROM FixedAssets f >INNER JOIN InvoicesWork i ON i.AssetID = f.AssetID >WHERE f.JobID = "#qryGetInvoice.JobID#" >AND f.AssetID IN (select AssetID FROM InvoiceWork WHERE condition to >get assets from InvoiceWork that you want ) >AND f.AssetID NOT IN (select AssetID FROM InvoiceWork WHERE condition >to get assets from InvoiceWork that you DO NOT want ) > > >I'm guessing on a few things here, but if you list out your table and >columns that we're talking about here and confirm the conditions, I'm sure >you could get this all in one query instead of using QofQ (unless of course >you're talking about different datasources, which it doesn't look like you >are). > >Dave Phillips >http://www.dave-phillips.com/ > Thanks for the reply but it doesn't work, If we have 3 different Assets, all sharing the same JobID, two of which have the same InvoiceID (The other is not in the InvoicesWork table) and we employ the qry... SELECT f.AssetID, f.name, i.text, i.amount FROM FixedAssets f INNER JOIN InvoicesWork i ON i.AssetID = f.AssetID WHERE f.JobID = "#qryGetInvoice.JobID#" AND f.AssetID IN (SELECT AssetID FROM FixedAssets WHERE FixedAssets.JobID="#qryGetInvoice.JobID#") The output is only the two assets with an InvoiceID (In the InvoicesWork table. It should be all three If we employ this qry... SELECT f.AssetID, f.name, i.text, i.amount FROM FixedAssets f INNER JOIN InvoicesWork i ON i.AssetID = f.AssetID WHERE f.JobID = "#qryGetInvoice.JobID#" AND f.AssetID IN (SELECT AssetID FROM FixedAssets WHERE FixedAssets.JobID="#qryGetInvoice.JobID#") AND f.AssetID NOT IN (SELECT AssetID FROM InvoicesWork WHERE InvoiceID="#qryGetInvoice.InvoiceID#") the output is empty and I want it to be the Asset not in the InvoicesWork table I suspect the join isn't working because the tables don't share the same field names but I dunno. Here's the tables: The Fixed Asset Table: CREATE TABLE `FixedAssets` ( `AssetID` smallint(5) NOT NULL auto_increment, `JobID` smallint(5) default '0', `Name` varchar(20) default NULL, PRIMARY KEY (`AssetID`), KEY `jobidx` (`JobID`) ) TYPE=InnoDB; The InvoicesWork Table: CREATE TABLE `InvoicesWork` ( `AssetID` smallint(5) default '0', `InvoiceID` smallint(5) NOT NULL, `Amount` float(10,2) default NULL, `Text` varchar(75) NOT Null, KEY `estidx`(`InvoiceID`) ) TYPE=InnoDB; waddya think? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311473 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: query of queries or something else
>How about something like this: > > >SELECT f.AssetID, f.name, i.text, i.amount >FROM FixedAssets f >INNER JOIN InvoicesWork i ON i.AssetID = f.AssetID >WHERE f.JobID = "#qryGetInvoice.JobID#" >AND f.AssetID IN (select AssetID FROM InvoiceWork WHERE condition to >get assets from InvoiceWork that you want ) >AND f.AssetID NOT IN (select AssetID FROM InvoiceWork WHERE condition >to get assets from InvoiceWork that you DO NOT want ) > > >I'm guessing on a few things here, but if you list out your table and >columns that we're talking about here and confirm the conditions, I'm sure >you could get this all in one query instead of using QofQ (unless of course >you're talking about different datasources, which it doesn't look like you >are). > >Dave Phillips >http://www.dave-phillips.com/ > >If a qry a couple of tables: > > > >SELECT AssetID, Name >FROM FixedAssets >WHERE FixedAssets.JobID="#qryGetInvoice.JobID#" > > > > > >SELECT AssetID, Text, Amount >FROM InvoicesWork >WHERE InvoiceID="#qryGetInvoice.InvoiceID#" > > >I now need a query that will extract any Asset from the Table FixedAssets >that is included in qryGetJobProperties but not in qryGetInvoiceProperties >so that I can display it in a dynamic form. > >Something like this; > >SELECT qryGetJobProperties.AssetID, > qryGetJobProperties.Name, > qryGetInvoiceProperties.AssetID >FROM qryGetJobProperties, qryGetInvoiceProperties >WHERE qryGetInvoiceProperties.AssetID != qryGetJobProperties.AssetID > > >I have also tried Nested Loops and Unions but am really struggling, >everything I try doesn't seem to make sense. I could mess about with an >array but is there really any need? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311472 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: query of queries or something else
How about something like this: SELECT f.AssetID, f.name, i.text, i.amount FROM FixedAssets f INNER JOIN InvoicesWork i ON i.AssetID = f.AssetID WHERE f.JobID = "#qryGetInvoice.JobID#" AND f.AssetID IN (select AssetID FROM InvoiceWork WHERE condition to get assets from InvoiceWork that you want ) AND f.AssetID NOT IN (select AssetID FROM InvoiceWork WHERE condition to get assets from InvoiceWork that you DO NOT want ) I'm guessing on a few things here, but if you list out your table and columns that we're talking about here and confirm the conditions, I'm sure you could get this all in one query instead of using QofQ (unless of course you're talking about different datasources, which it doesn't look like you are). Dave Phillips http://www.dave-phillips.com/ -Original Message- From: Andrew Whone [mailto:[EMAIL PROTECTED] Sent: Saturday, August 23, 2008 3:47 PM To: CF-Talk Subject: query of queries or something else If a qry a couple of tables: SELECT AssetID, Name FROM FixedAssets WHERE FixedAssets.JobID="#qryGetInvoice.JobID#" SELECT AssetID, Text, Amount FROM InvoicesWork WHERE InvoiceID="#qryGetInvoice.InvoiceID#" I now need a query that will extract any Asset from the Table FixedAssets that is included in qryGetJobProperties but not in qryGetInvoiceProperties so that I can display it in a dynamic form. Something like this; SELECT qryGetJobProperties.AssetID, qryGetJobProperties.Name, qryGetInvoiceProperties.AssetID FROM qryGetJobProperties, qryGetInvoiceProperties WHERE qryGetInvoiceProperties.AssetID != qryGetJobProperties.AssetID I have also tried Nested Loops and Unions but am really struggling, everything I try doesn't seem to make sense. I could mess about with an array but is there really any need? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311469 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries
Did you check the logs to see if there were any errors starting ColdFusion when QofQ's stopped working? (Not the run-time error, but an actual startup one) ~Brad - Original Message - From: "heath stein" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Tuesday, August 05, 2008 9:21 AM Subject: Re: Query of Queries > Query of Queries working again > > I did one more restart before I was going to do a re-install and Query of > Queries magically started working again. Jrun must have not been starting > a process or something. > > Heath ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310222 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries
>>I am afraid your right we might have to do a re-install. If restarting CF fails, and BEFORE reinstalling CF, one could try to restart the server. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310215 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries
cross fingers and start preparing reinstall plan heh ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310214 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries
Query of Queries working again I did one more restart before I was going to do a re-install and Query of Queries magically started working again. Jrun must have not been starting a process or something. Heath ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310212 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries
Thanks Dave, I am afraid your right we might have to do a re-install. Thanks Heath ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310210 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries
good thing it's the development server :) what if you just try: select 1 as id no datasources involved. no sql involved. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310211 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query of Queries
Yikes! Have you tried it with a different datasource? I can't imagine it is something with the way the database is returning the records, or even the driver for that matter, however, just to eliminate other possibilities. I'd try it with a different datasource (of the same type) and then if you still get it, a different datasource (of a different type), to rule that out too. If that still fails, then, since it is a development server, I would go ahead and reinstall CF and see if that solves the problem. If that doesn't work either, then I'm not sure what else to do short of deleting your entire installation and starting with a fresh new install. I'm sure you've thought of all that already, but I tried googling (as you have as well, I'm sure) and don't find any solutions out there on this, and I'm sure you don't want to be down longer than you need to be. Sorry I can't be of more help! Sincerely, Dave Phillips -Original Message- From: heath stein [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2008 8:55 AM To: CF-Talk Subject: Re: Query of Queries Thanks Dave, I actually did this out side of the current app, on a new table and got the same error. Heath Stein ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310209 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries
Thanks Dave, I actually did this out side of the current app, on a new table and got the same error. Heath Stein ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310208 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries
Heath, Can you run the following in a test.cfm file on your server (OUTSIDE OF YOUR EXISTING APP): SELECT ID FROM WHATEVER (just use a simple query here) SELECT ID FROM qTest Just trying to narrow down the issue. Sincerely, Dave Phillips -Original Message- From: heath stein [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2008 8:27 AM To: CF-Talk Subject: Re: Query of Queries Acctuly none of the tables have changed at all. Also every query that was using query of queries stoped working. Below is the error. java.sql.SQLException: String index out of range: 17 at coldfusion.sql.JdbcImpl.getConnection(JdbcImpl.java:73) at coldfusion.sql.JdbcImpl.getConnection(JdbcImpl.java:56) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:214) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:447) at cfdsp_lesson_interface2ecfm1632308106.runPage(/nterface/dsp_lesson_interface ..cfm:31) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:147) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:357) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:62) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:107) at coldfusion.filter.PathFilter.invoke(PathFilter.java:80) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:47) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistence Filter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:35) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:43) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.CfmServlet.service(CfmServlet.java:105) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91) at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42) at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:252) at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527) at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:192) at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java: 348) at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:451 ) at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:29 4) at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66) >Have any of the tables changed? Could be that if you're using cfqueryparam >on the original queries and the tables have changed, there might be a column >mis-match. > >Adrian > >Today our development server got hung up. We had to restart coldfusion and >after the restart all queries using query of queries stopped working. The >error message says (string out of index 7). Our development server is a >linux box with cf 6 installed. If any one has come across this issue please >let me know. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310207 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries
Acctuly none of the tables have changed at all. Also every query that was using query of queries stoped working. Below is the error. java.sql.SQLException: String index out of range: 17 at coldfusion.sql.JdbcImpl.getConnection(JdbcImpl.java:73) at coldfusion.sql.JdbcImpl.getConnection(JdbcImpl.java:56) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:214) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:447) at cfdsp_lesson_interface2ecfm1632308106.runPage(/nterface/dsp_lesson_interface.cfm:31) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:147) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:357) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:62) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:107) at coldfusion.filter.PathFilter.invoke(PathFilter.java:80) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:47) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:35) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:43) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.CfmServlet.service(CfmServlet.java:105) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91) at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42) at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:252) at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527) at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:192) at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:348) at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:451) at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:294) at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66) >Have any of the tables changed? Could be that if you're using cfqueryparam >on the original queries and the tables have changed, there might be a column >mis-match. > >Adrian > >Today our development server got hung up. We had to restart coldfusion and >after the restart all queries using query of queries stopped working. The >error message says (string out of index 7). Our development server is a >linux box with cf 6 installed. If any one has come across this issue please >let me know. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310204 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries
Have any of the tables changed? Could be that if you're using cfqueryparam on the original queries and the tables have changed, there might be a column mis-match. Adrian -Original Message- From: heath stein [mailto:[EMAIL PROTECTED] Sent: 05 August 2008 04:41 To: CF-Talk Subject: Query of Queries Today our development server got hung up. We had to restart coldfusion and after the restart all queries using query of queries stopped working. The error message says (string out of index 7). Our development server is a linux box with cf 6 installed. If any one has come across this issue please let me know. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310188 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries Issue
> -Original Message- > From: Flesher, Rob [USA] [mailto:[EMAIL PROTECTED] > Sent: Friday, December 28, 2007 12:29 PM > To: CF-Talk > Subject: Query of Queries Issue > > All, > > Please forgive me if you get this twice, as it did not appear to post > the first time. > > I am having an issue with cold fusion query of queries when upgrading > to > MX7. Previously, when doing a SUM(querycol) in a query of queries when > all the values of the column were null, the sum would return a 0. In > MX7, it is returning a blank string, causing all calculations to error > out. > > Can someone tell me if CF query of queries has a function similar to > oracle's NVL? Basically, what I need to do is NVL(SUM(querycol), 0) to > insure any null values return as a 0. > > If a function like this doesn't exist, how would you recommend I deal > with this issue? Well - I'm sure you could do an "if" outside the query (you can modify queries manually like any other data structure) but I assume you'd prefer something in the query itself. I'm not sure but can you do math in the query? Something like " SUM(querycol) + 0" - that should (if it's allowed trigger CFs automatic type conversion to create a number for you). For that matter having a zero in any of the rows should also do it... perhaps adding a dummy row with a zero value into the original query might be possible? Something to try at least. Jim Davis ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295490 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries Issue
Maybe doing a CAST on the column will help? CF may be seeing the nulls and assuming the column is a varchar instead of an int. On Dec 28, 2007 11:28 AM, Flesher, Rob [USA] <[EMAIL PROTECTED]> wrote: > All, > > Please forgive me if you get this twice, as it did not appear to post > the first time. > > I am having an issue with cold fusion query of queries when upgrading to > MX7. Previously, when doing a SUM(querycol) in a query of queries when > all the values of the column were null, the sum would return a 0. In > MX7, it is returning a blank string, causing all calculations to error > out. > > Can someone tell me if CF query of queries has a function similar to > oracle's NVL? Basically, what I need to do is NVL(SUM(querycol), 0) to > insure any null values return as a 0. > > If a function like this doesn't exist, how would you recommend I deal > with this issue? > > Thanks, > Rob Flesher > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295491 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries causing server to hang
Graham, I haven't seen this exact error before, but do you perchance have a around the QoQ code? Query of queries are very expensive on the server and grow more so as the record set being manipulated increases. Many times, the same functions performed in QoQ can be performed by rewriting the SQL used in the original query to return a more efficient query from the start. If you must use QoQ with a large record set, you might try adding caching to the query to lower the overhead or scoping the manipulated record set as appropriate for later re-use. The lock may be increasing the problem as the thread count stacks up while waiting for the ability to lock. Once that happens, and CF reaches the maximum number of requests (as configured in the CF Admin) the initial request may not finish and the memory used to manipulate and handle the query record set may not be cleared for other requests. I had to do some SQL optimizing last year on a client site which was having similar issues. HTH, Jon On Oct 29, 2007, at 8:50 AM, graham bates wrote: > Over the last few weeks we have noticed our servers starting to > queue up and often not recover; upon inspecting the stack trace, we > invariably see lines like the following; > > Thread "jrpp-485": > - waiting to lock <0x60597d10> (a java.lang.Class) > Runing a CFQUERY > at D:\Live\wwwroot\admin\blah.cfm:109 > Thread "jrpp-484": > - waiting to lock <0x60597d10> (a java.lang.Class) > Runing a CFQUERY > at D:\Live\wwwroot\admin\data\blah2.cfm:104 > Thread "jrpp-483": > - waiting to lock <0x60597d10> (a java.lang.Class) > Runing a CFQUERY > at D:\Live\cfc\outsource\blah3.cfc:53 > > > What is interesting, is that when these line numbers are followed, > they are always pointing to **Query of Queries**. Has anyone come > across this kind of thing before, my searches on these and other > forums reveal nothing. > > Many Thanks > > > > ~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292276 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: query of queries and column aliasing
Idiot alert, idiot alert! My alias name was "DESC"... short for "description" Also a reserved word =) Funny how things work fine when you don't use reserved words. Rick ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285773 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: query of queries and column aliasing
Try "col_alias = expression" Mark -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 6:17 PM To: CF-Talk Subject: query of queries and column aliasing So, it looks like column aliasing isn't valid in query of queries. For example, the following works: select foo + ' - ' + foo2 from qry But this does not: select foo + ' - ' + foo2 as newfoo from qry Coldfusion seems to automatically alias the dynamic columns .. as "Column_1" in the case above. But.. is there any way to do column aliasing in QoQ that I'm missing, or is it just not supported? (BTW I'm using CFMX 7.0.2) -- Rick Root Check out CFMBB, BlogCFM, ImageCFC, ImapCFC, CFFM, and more at www.opensourcecf.com ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285770 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries date comparison
>>I think he is working with dates You're right, I didn't notice the CFMX form of the function. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284933 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries date comparison
Claude, I think he is working with dates; I think the data-type for that column in Querynew() was "date". .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Monday, July 30, 2007 10:48 AM To: CF-Talk Subject: Re: Query of Queries date comparison . and yes, since you are actually working with strings, not dates, you do need quotes in WHERE date > '#DateFormat(Now(),"/mm/dd")#' ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284826 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries date comparison
and yes, since you are actually working with strings, not dates, you do need quotes in WHERE date > '#DateFormat(Now(),"/mm/dd")#' -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284803 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries date comparison
>>I would like to run a query on this query where it finds only the records where the date is greater than today. You cannot compare dates if they are stored in American format. Use QuerySetCell(event, "date", 2007/07/28, 1) Instead: always with mask "/mm/dd" Then WHERE date > #DateFormat(Now(),"/mm/dd")# (you can even forget about slashes) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284802 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries date comparison
I think you need quotes around your date in the comparison in the SQL. But I always have trouble with QoQ syntax for some reason. --Ben Doom Mark Lewis wrote: > Hi, > > Wondered if someone could advise on this problem I am having. > > I have created a query as shown below... > > > > > > > > > > > > > > > > I would like to run a query on this query where it finds only the records > where the date is greater than today. > > I have written the following... > > >SELECT * >FROM event >WHERE date > #DateFormat(Now(),"mm/dd/")# > > > However I get an error returned > > "Query Of Queries syntax error. > Encountered "date. Incorrect conditional expression, Expected one of > [like|null|between|in|comparison] condition, " > > Does this mean I cannot use this greater than ( > ) clause on a query of > queries, I have searched for some documentation but couldnt find any. I f > anyone knows where any could be posted on the web, or a solution to this > problem that would be great. > > Thank you in advance > > Mark > > ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284794 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries date comparison
Oh sorry, I only just got everyone else's responses through, thank you to you guys for your time too. Much appreciated, Mark ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284796 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries date comparison
Gualtiero and Dave thanks for your help I have resolved the issue with both your tips. Thank you for your time again Mark > "Date" is possibly(probably) a reserved word? Even if it isn't, your test > will fail when the year rolls over - you should test /mm/dd. And I > think you might need apostrophes around today's formatted date? > > None of this is guaranteed, I am famous for being wrong. > ~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284795 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries date comparison
Mark, I think the problem is the "date" column. Date is a reserved word, I think, and must be wrapped in []. Also, I would use a query param as it takes care of the data type conversion. SELECT * FROM event WHERE [date] > .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Mark Lewis [mailto:[EMAIL PROTECTED] Sent: Monday, July 30, 2007 7:43 AM To: CF-Talk Subject: Query of Queries date comparison Hi, Wondered if someone could advise on this problem I am having. I have created a query as shown below... I would like to run a query on this query where it finds only the records where the date is greater than today. I have written the following... SELECT * FROM event WHERE date > #DateFormat(Now(),"mm/dd/")# However I get an error returned "Query Of Queries syntax error. Encountered "date. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, " Does this mean I cannot use this greater than ( > ) clause on a query of queries, I have searched for some documentation but couldnt find any. I f anyone knows where any could be posted on the web, or a solution to this problem that would be great. Thank you in advance Mark ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284790 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries date comparison
Not too sure about this, but have you tried it with GT maybe also try changing the filed name from date to tripDate - in case it is a reserved word On 7/30/07, Mark Lewis <[EMAIL PROTECTED]> wrote: > > Hi, > > Wondered if someone could advise on this problem I am having. > > I have created a query as shown below... > > > varchar")> > > > > > > > > > > > > > I would like to run a query on this query where it finds only the records > where the date is greater than today. > > I have written the following... > > >SELECT * >FROM event >WHERE date > #DateFormat(Now(),"mm/dd/")# > > > However I get an error returned > > "Query Of Queries syntax error. > Encountered "date. Incorrect conditional expression, Expected one of > [like|null|between|in|comparison] condition, " > > Does this mean I cannot use this greater than ( > ) clause on a query of > queries, I have searched for some documentation but couldnt find any. I f > anyone knows where any could be posted on the web, or a solution to this > problem that would be great. > > Thank you in advance > > Mark > > ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284791 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query of Queries date comparison
"Date" is possibly(probably) a reserved word? Even if it isn't, your test will fail when the year rolls over - you should test /mm/dd. And I think you might need apostrophes around today's formatted date? None of this is guaranteed, I am famous for being wrong. -Original Message- From: Mark Lewis [mailto:[EMAIL PROTECTED] Sent: Monday, July 30, 2007 7:43 AM To: CF-Talk Subject: Query of Queries date comparison Hi, Wondered if someone could advise on this problem I am having. I have created a query as shown below... I would like to run a query on this query where it finds only the records where the date is greater than today. I have written the following... SELECT * FROM event WHERE date > #DateFormat(Now(),"mm/dd/")# However I get an error returned "Query Of Queries syntax error. Encountered "date. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, " Does this mean I cannot use this greater than ( > ) clause on a query of queries, I have searched for some documentation but couldnt find any. I f anyone knows where any could be posted on the web, or a solution to this problem that would be great. Thank you in advance Mark ~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284787 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries date comparison
You can try using data object instead of a string formatted as a date. I think the column in your query is of type string. Bye Gualtiero - Original Message - From: "Mark Lewis" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Monday, July 30, 2007 1:43 PM Subject: Query of Queries date comparison > Hi, > > Wondered if someone could advise on this problem I am having. > > I have created a query as shown below... > > > varchar")> > > > > > > > > > > > > > I would like to run a query on this query where it finds only the records > where the date is greater than today. > > I have written the following... > > > SELECT * > FROM event > WHERE date > #DateFormat(Now(),"mm/dd/")# > > > However I get an error returned > > "Query Of Queries syntax error. > Encountered "date. Incorrect conditional expression, Expected one of > [like|null|between|in|comparison] condition, " > > Does this mean I cannot use this greater than ( > ) clause on a query of > queries, I have searched for some documentation but couldnt find any. I f > anyone knows where any could be posted on the web, or a solution to this > problem that would be great. > > Thank you in advance > > Mark > > ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284786 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: query of queries question
I would cache your initial result, and build a set of filters that are applied to it each time the user updates their filter. So, you would have cachedQuery as your initial base search, then filteredQuery that you would not cache but would always be the result of a QoQ of your cached query with updated filters applied against it. Chris Peterson Gainey IT Adobe Certified Advanced Coldfusion Developer ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282539 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query of Queries Question
Tried thatI did finally find it in the docs. You can actually specify your own escape character. So it's like this: SELECT * FROM whatever WHERE what LIKE '%95\%%' ESCAPE '\' This will find anything that has 95% in the resultset 'whatever', column 'what'. It doesn't matter what character you use. Of course, you want to make sure that character is not in your text you are queryingso I ended up doing this: SELECT * FROM whatever WHERE what LIKE '%95#chr(9)#%%' ESCAPE '#chr(9)#' Ascii Character 9 is the TAB character and in my circumstance it works well because I'm comparing URLs and they don't contain tab characters. Hope this helps someone else who runs into the same thing! Dave -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Monday, February 26, 2007 10:36 AM To: CF-Talk Subject: RE: Query of Queries Question This is just a guess, but you can try putting either a "\" or a "'" before the characters that needs to be escaped?? LIKE 'foo\%bar' Or maybe LIKE 'foo'%bar' The single quote is only a guess because single quote escaped another single quote. ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270716 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query of Queries Question
This is just a guess, but you can try putting either a "\" or a "'" before the characters that needs to be escaped?? LIKE 'foo\%bar' Or maybe LIKE 'foo'%bar' The single quote is only a guess because single quote escaped another single quote. .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Dave Phillips [mailto:[EMAIL PROTECTED] Sent: Monday, February 26, 2007 11:32 AM To: CF-Talk Subject: Query of Queries Question Hi, Anyone know how to force a LIKE comparison in a QofQ query to look for literals of the following special characters: % _ [ ] ^ All of the above are characters used in a LIKE comparison, but my string has several of them in it, so I need to 'escape' them somehow. I'm on MX 6.1. Thanks! Sincerely, Dave Phillips President WebTech Staffing, LLC [EMAIL PROTECTED] (817) 473-2119 ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270676 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries Problem with JDBC
Hello Jochem, The error I am getting is that ColdFusion MX gets an error executing Query of Queries. Well, here's my testing code: --- SELECT a.* FROMstatscountries a JOIN( SELECT isocode FROMstatscountries WHERE isocode LIKE '%A%' ) b ON a.isocode = b.isocode WHERE a.isocode LIKE '%U%' SELECT * FROMqryName WHERE isocode LIKE '%U%' #qofq.RecordCount# - Then, the error I get when I execute it is: = Error Occurred While Processing Request Error Executing Database Query. The error occurred in /var/www/html/circus/index.cfm: line 14 12 : 13 : 14 : 15 : SELECT * 16 : FROM qryName SQLSELECT * FROM qryName WHERE isocode LIKE '%U%' java.sql.SQLException at coldfusion.sql.JdbcImpl.getConnection(JdbcImpl.java:73) at coldfusion.sql.JdbcImpl.getConnection(JdbcImpl.java:56) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:207) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:447) at cfindex2ecfm1734096888.runPage(/var/www/html/circus/index.cfm:14) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:147) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:357) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:62) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:107) at coldfusion.filter.PathFilter.invoke(PathFilter.java:80) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:47) at coldfusion.filter.BrowserDebugFilter.invoke(BrowserDebugFilter.java:52) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:35) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:43) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.CfmServlet.service(CfmServlet.java:105) at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91) at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42) at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:252) at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527) at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:192) at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:348) at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:451) at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:294) at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66) == The problem shoots randomly after the CFMX server starts. Sometimes it occurs in a fluid succession, sometimes it seems fixed but would get crappy after one restart. I've also tried dropping in the JDBC drivers at those directories, then restart the server, but I still get the error. As for using a 7.4 JDBC to access an 8.1 database, it gets an error on some encoding, particularly on trailing spaces, which I don't get when I use an 8.1 JDBC. On using PostgreSQL 8.2 with CFMX, I haven't tried it, and I am not sure if the RPMs would install properly on my FC4. If I remember it right, I think I have tried installing 8.2 and the RPMs went searching for some .so# that I don't have. Does CFMX really use JDBC2? I thought JVM 1.4 uses JDBC3. Isn't the JVM that comes with ColdFusion version 1.4? So shouldn't I use JDBC3 (although JDBC2 works, anyway). Anyways, thanks for the advices, and I'll see 8.2. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267569 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries Problem with JDBC
Jerky San Pedro wrote:
> I am getting a Query of Query error in CFMX 6.1 when I used a JDBC
> driver for PostgreSQL 8.1.
Which error exactly?
> I tested CFMX 6.1 in two setups:
>
> One is an FC4 server
> The other is a RHEL4 server
>
> In FC4, I installed PostgreSQL 8.1 and connected CFMX using the
> recommended JDBC3 and JDBC2, each in alternate trials. Both trials
> issued a query of queries (QofQ) error, which I heard of is inherent
> in FC4. With that, I removed PostgreSQL 8.1 and downgraded to 7.4 and
> used the recommended JDBC for 7.4. It worked fine. I don't get any
> QofQ errors.
Have you tried the combination of CF 6.1, PostgreSQL 8.2.1 and the
postgresql-8.2-504.jdbc3.jar driver?
> In RHEL4, I installed PostgreSQL 7.4 first. It worked fine. No QofQ
> errors. I wasn't able to fully install PostgreSQL version 8.1 for
> RHEL4. Thus I tried something else: I removed the JDBC JVM mapping for
> 7.4 in CFMX administrator and installed an 8.1 JDBC2 JVM mapping. I
> connected to a remote PostgreSQL 8.1 database. It worked. No errors.
> QofQ OK. Problem is my database is remote, which is a different
> scenario from my FC4's setup. My problem at this point is semi-solved.
What happens if you don't register the PostgreSQL JDBC driver in your
classpath, but just drop it in the /WEB-INF/cfusion/lib/ directory and restart
the instance?
> But since the best scenario is sticking to PostgreSQL 7.4, I might
> just stick to it.
> 1. If I am using simple queries available, would my using a 7.4 JDBC
> driver to connect to an 8.1 database cause any problems?
PostgreSQL servers and JDBC drivers each support multiple versions of the
protocol. Any JDBC driver for 7.4 or later will connect to any server version
7.4 or later. I would not downgrade the server just because I needed an older
driver.
> 2. Has anyone encountered this?
No
> 3. How will you know what JDBC driver is suitable for you? I thought
> CFMX comes with its own JDK, which is version 1.4, thus JDBC3. How
> come JDBC2 works well, or does it really?
Because CF MX / 7 only uses JDBC2.
Jochem
PS Have you applied for the Scorpio beta yet?
https://prerelease.adobe.com/callout/apply.html?callid={E9F64ADB-DADA-485E-BFFE-60E0D783EBEF}
~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267444
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries Problem with JDBC
Thanks, then. I guess I'd better stick to 7.4 and MX for the mean time. >> 2. Has anyone encountered this? What did you do? > >I haven't encountered this. But there are a couple of things I would >suggest. > >First, have you considered upgrading to CFMX 7.0.2? > >Second, have you considered upgrading the JVM? You should be able to upgrade >to a later 1.4.2 JVM. > >> 3. How will you know what JDBC driver is suitable for you? I >> thought CFMX comes with its own JDK, which is version 1.4, >> thus JDBC3. How come JDBC2 works well, or does it really? > >CFMX does come with its own JVM, which for CFMX 6.1 is 1.4.2 base or >1.4.2_05, I forget which. The CF Administrator will tell you what JVM >version you're using. > >Newer JVMs should still be able to run JDBC2 applications, I think. > >And, as for which JDBC driver is suitable, that's really up in the air. If >you can use the JDBC drivers that come with CF, well, most people just do >that. That obviously won't work with PostgreSQL, so you use whatever works >for you. I've seen all sorts of different JDBC drivers used with CF, and >there's typically no one "best answer" to this question. I've seen some odd >cases where the ODBC bridge provides better performance than the type 4 JDBC >drivers, and frankly that's just crazy to me. > >Dave Watts, CTO, Fig Leaf Software >http://www.figleaf.com/ > >Fig Leaf Software provides the highest caliber vendor-authorized >instruction at our training centers in Washington DC, Atlanta, >Chicago, Baltimore, Northern Virginia, or on-site at your location. >Visit http://training.figleaf.com/ for more information! ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267430 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries Problem with JDBC
> 2. Has anyone encountered this? What did you do? I haven't encountered this. But there are a couple of things I would suggest. First, have you considered upgrading to CFMX 7.0.2? Second, have you considered upgrading the JVM? You should be able to upgrade to a later 1.4.2 JVM. > 3. How will you know what JDBC driver is suitable for you? I > thought CFMX comes with its own JDK, which is version 1.4, > thus JDBC3. How come JDBC2 works well, or does it really? CFMX does come with its own JVM, which for CFMX 6.1 is 1.4.2 base or 1.4.2_05, I forget which. The CF Administrator will tell you what JVM version you're using. Newer JVMs should still be able to run JDBC2 applications, I think. And, as for which JDBC driver is suitable, that's really up in the air. If you can use the JDBC drivers that come with CF, well, most people just do that. That obviously won't work with PostgreSQL, so you use whatever works for you. I've seen all sorts of different JDBC drivers used with CF, and there's typically no one "best answer" to this question. I've seen some odd cases where the ODBC bridge provides better performance than the type 4 JDBC drivers, and frankly that's just crazy to me. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267418 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
You could also add a RowNumber column to your query: Then, do: SELECT * FROM qSomeQuery WHERE RowNumber >= 21 AND RowNumber < 41 ORDERY BY LastName Not sure what the performance hit on doing this would be. Jon On 1/5/07, Dave Phillips <[EMAIL PROTECTED]> wrote: > Does anyone know if it's possible to extract a 'subset' of records in a query > utilizing Query of Queries? Basically I want to do something like this: > > > SELECT * > FROM qSomeQuery > WHERE currentRow >= 21 AND currentRow < 41 > ORDERY BY LastName > > > Of course, this doesn't work because currentrow does not specify the row > number inside of a Query, only when you are working with the query results > directly (like qSomeQuery.currentRow). > > Anyone know how this is possible? The only thing I can think of doing is > something like this: > > >*** In here I would build a new query with only the rows available as I > loop through with functions like queryAddRow() and querySetCell(). > > > This idea would not be very efficient, so I'm looking for an alternative > method. > > Thanks for your help in advance! > > Dave > > ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265888 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries question
Or even easier, without the loop. SELECT * FROM qMyQuery WHERE id IN (#list_id#) -- Russ -Original Message- From: Terry Sta. Maria [mailto:[EMAIL PROTECTED] Sent: 05 January 2007 20:52 To: CF-Talk Subject: Re: Query of Queries question You don't necessarily have to create a query from scratch. Instead, if there is a column (with a simple datatype) that uniquely identifies each row, this can be done with a list and a query of queries. If qMyQuery.id is such a column, then you can get those all into a list: Then you can do your query of queries with this list: SELECT * FROM qMyQuery WHERE id IN (#list_id#) If your id column isn't numeric, you can loop through the list creating "LIKE" clauses for string, or similar comparisons for other simple data types. Hope this helps. ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265875 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
You don't necessarily have to create a query from scratch. Instead, if there is a column (with a simple datatype) that uniquely identifies each row, this can be done with a list and a query of queries. If qMyQuery.id is such a column, then you can get those all into a list: Then you can do your query of queries with this list: SELECT * FROM qMyQuery WHERE id IN (#list_id#) If your id column isn't numeric, you can loop through the list creating "LIKE" clauses for string, or similar comparisons for other simple data types. Hope this helps. ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265872 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries question
The query is not coming from a DB. It is actually coming from a CFSEARCH tag, so unfortunately, I can't use this method. >not sure what DB you're using.. but with Oracle you can do this in a subquery >select * from >( >select rownum as rn, n.* >From navmenu n >) >where rn >= 5 and rn <= 7 > > > >On 1/5/07, Dave Phillips <[EMAIL PROTECTED]> wrote: >> ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265870 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
not sure what DB you're using.. but with Oracle you can do this in a subquery select * from ( select rownum as rn, n.* >From navmenu n ) where rn >= 5 and rn <= 7 On 1/5/07, Dave Phillips <[EMAIL PROTECTED]> wrote: > Does anyone know if it's possible to extract a 'subset' of records in a query > utilizing Query of Queries? Basically I want to do something like this: > > > SELECT * > FROM qSomeQuery > WHERE currentRow >= 21 AND currentRow < 41 > ORDERY BY LastName > > > Of course, this doesn't work because currentrow does not specify the row > number inside of a Query, only when you are working with the query results > directly (like qSomeQuery.currentRow). > > Anyone know how this is possible? The only thing I can think of doing is > something like this: > > >*** In here I would build a new query with only the rows available as I > loop through with functions like queryAddRow() and querySetCell(). > > > This idea would not be very efficient, so I'm looking for an alternative > method. > > Thanks for your help in advance! > > Dave > > ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265817 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
Does the result /have/ to be a query? Could you make a structure instead, using your cfoutput method? That should be pretty quick. On 1/5/07, Dave Phillips <[EMAIL PROTECTED]> wrote: > Does anyone know if it's possible to extract a 'subset' of records in a query > utilizing Query of Queries? Basically I want to do something like this: > > > SELECT * > FROM qSomeQuery > WHERE currentRow >= 21 AND currentRow < 41 > ORDERY BY LastName > > > Of course, this doesn't work because currentrow does not specify the row > number inside of a Query, only when you are working with the query results > directly (like qSomeQuery.currentRow). > > Anyone know how this is possible? The only thing I can think of doing is > something like this: > > >*** In here I would build a new query with only the rows available as I > loop through with functions like queryAddRow() and querySetCell(). > > > This idea would not be very efficient, so I'm looking for an alternative > method. > > Thanks for your help in advance! > > Dave > > ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265815 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries - showing all entries from DB1
At 09:29 AM 8/18/2006, Kevin Roche wrote: >I think its just a matter of: > >1/ Do an Inner Join with QonQ > >2/ Do a QonQ query on the left hand table that creates an exact similar >result set with null entries for any right table fields and omits any rows >from the first query. > >3/ Do a QonQ which does a UNION of the the first two. > >You may have problems with this if the results of 1 are large and can't be >converted quickly into a list. Well, I was hoping there would be an easier way. I guess I can import the table in DB2 into DB1 and just do a usual left join with the two tables now inside the same DB. I was hoping the QoQ syntax would allow for a left outer join, but it looks like it doesn't. :-( Thanks, Roberto ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250375 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries - showing all entries from DB1
Roberto, I would suggest a similar series of steps but the link gives a rather complex example since they are trying to write a generic version that works out foir itself waht the queries are. If you do it yourself it may be easier to understand, and it may be possible to make it perform better. I think its just a matter of: 1/ Do an Inner Join with QonQ 2/ Do a QonQ query on the left hand table that creates an exact similar result set with null entries for any right table fields and omits any rows from the first query. 3/ Do a QonQ which does a UNION of the the first two. You may have problems with this if the results of 1 are large and can't be converted quickly into a list. Kevin -Original Message- From: Roberto Perez [mailto:[EMAIL PROTECTED] Sent: 18 August 2006 12:58 To: CF-Talk Subject: RE: Query of Queries - showing all entries from DB1 At 07:30 AM 8/18/2006, you wrote: >I think with a query of queries, you can do a left outer join using "*=" >syntax: > >SELECT > * >FROM > table1, > table2 >WHERE > table1.id *= table2.fkey > >I AM NOT SURE OF THIS. But I think I read it somewhere. Thanks for the input. I tried it, but CFMX did not like the syntax (I'm using Access, if that makes a difference). I found a link to a "fake left outer join" script at http://instantbadger.blogspot.com/2006/07/faking-left-outer-join-in-query-of ..html A left outer join sound just like what I need to do. Is there an easier way to do it (easier than what is described in the link above)? Thanks in advance, Roberto Perez [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250287 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries - showing all entries from DB1
I was going to suggest a union, so that link you found is the way to go IMO. On 8/18/06, Roberto Perez <[EMAIL PROTECTED]> wrote: > I found a link to a "fake left outer join" script at > http://instantbadger.blogspot.com/2006/07/faking-left-outer-join-in-query-of.html > > A left outer join sound just like what I need to do. Is there an > easier way to do it (easier than what is described in the link above)? -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250279 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries - showing all entries from DB1
At 07:30 AM 8/18/2006, you wrote: >I think with a query of queries, you can do a left outer join using "*=" >syntax: > >SELECT > * >FROM > table1, > table2 >WHERE > table1.id *= table2.fkey > >I AM NOT SURE OF THIS. But I think I read it somewhere. Thanks for the input. I tried it, but CFMX did not like the syntax (I'm using Access, if that makes a difference). I found a link to a "fake left outer join" script at http://instantbadger.blogspot.com/2006/07/faking-left-outer-join-in-query-of.html A left outer join sound just like what I need to do. Is there an easier way to do it (easier than what is described in the link above)? Thanks in advance, Roberto Perez [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250273 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query of Queries - showing all entries from DB1
I think with a query of queries, you can do a left outer join using "*=" syntax: SELECT * FROM table1, table2 WHERE table1.id *= table2.fkey I AM NOT SURE OF THIS. But I think I read it somewhere. The "*=" says select all records from left. Conversely, you could do a "=*" to do a right outer join. ... Ben Nadel www.bennadel.com -Original Message- From: Roberto Perez [mailto:[EMAIL PROTECTED] Sent: Friday, August 18, 2006 12:57 AM To: CF-Talk Subject: Query of Queries - showing all entries from DB1 Hi all, I have a query of queries that brings Region information from DB1, and CityName and Customer information from DB2. The query looks like this: SELECT listRegions.regionID AS AregionID,listRegions.region AS Aregion, listCities.regionID AS BregionID,listCities.cityName AS BcityName, listCities.customer AS Bcustomer FROM listRegions, listCities WHERE listRegions.regionID = listCities.regionID ORDER BY listRegions.region ASC, listCities.cityName ASC Then I output the results with the following code: #Aregion# #BcityName# #Bcustomer# My problem: if a city has no customers, it does not get listed. But I want that city anyway, with an empty customer entry (a text like "no customer found" would be swell). Does a QoQ have a syntax similar to "left join", "right join", etc., to show all entries from one side of the relationship and only matching entries from the other? In this case, I'd like all city names, and only the matching customers for each city (with the cities that have no customers still displayed). Thanks in advance for any pointers and/or ideas. Regards, Roberto Perez [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250271 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries
Brian Rinaldi wrote: > Have you tried getting the query metadata to verify the column data types? > You can do getMetaData(query) in CF 7. You must've missed my post from about 6 hours ago: http://www.houseoffusion.com/cf_lists/messages.cfm/forumid:4/threadid:45923 My fault, I changed the title of the thread to "SOLVED: Query of Queries" and Michael's software picked it up as a new thread (pssst, hey michael - consider taking "SOLVED: " into consideration) Yeah, I tried that! =) Rick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240459 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries
Have you tried getting the query metadata to verify the column data types? You can do getMetaData(query) in CF 7. - Brian Rinaldi blog - www.remotesynthesis.com/blog cf open source list - www.remotesynthesis.com/cfopensourcelist Boston CFUG - www.bostoncfug.org On 5/12/06, Munson, Jacob <[EMAIL PROTECTED]> wrote: > > Oops, I was thinking of when I use cf functions on the right side of a > where clause. :\ > > > -Original Message- > > From: Rick Root [mailto:[EMAIL PROTECTED] > > Sent: Friday, May 12, 2006 11:18 AM > > To: CF-Talk > > Subject: Re: Query of Queries > > > > Munson, Jacob wrote: > > > Try using the val() function. > > > > val is a coldfusion function, not a SQL function. you can't > > use val() > > anymore than you can use lcase() or chr() in a query of queries. > > > > Rick > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240445 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query of Queries
Oops, I was thinking of when I use cf functions on the right side of a where clause. :\ > -Original Message- > From: Rick Root [mailto:[EMAIL PROTECTED] > Sent: Friday, May 12, 2006 11:18 AM > To: CF-Talk > Subject: Re: Query of Queries > > Munson, Jacob wrote: > > Try using the val() function. > > val is a coldfusion function, not a SQL function. you can't > use val() > anymore than you can use lcase() or chr() in a query of queries. > > Rick > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240369 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries
Munson, Jacob wrote: > Try using the val() function. val is a coldfusion function, not a SQL function. you can't use val() anymore than you can use lcase() or chr() in a query of queries. Rick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240365 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query of Queries
Why not CAST? Select SUM(CAST(option_price AS DECIMAL) * QUANTITY) as merch_charge -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 11:53 AM To: CF-Talk Subject: Re: Query of Queries I ran this query and got a different error: select SUM((OPTION_PRICE+0)) AS MERCH_CHARGE FROM contents Cannot mix types "VARCHAR" and "BIGINT" in a "+" binary operation so it looks like for some reason, coldfusion things "OPTION_PRICE" is a varchar, even though the column type in the database is "DECIMAL(10,2)" Rick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240362 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query of Queries
This wouldn't make much sense, but maybe it's that one is int and one is float... Try putting in SUM(1.0 * OPTION_PRICE) AS MERCH_CHARGE - and - SUM( 1 * OPTION_PRICE ) AS MERCH_CHARGE This way we will see if that breaks. If the 1 still breaks but the 1.0 does not break, then it's a casting issue?? ... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 212.691.3477 fax www.nylontechnology.com "You know, like nunchuck skills, bowhunting skills, computer hacking skills... Girls only want boyfriends who have great skills." - Napoleon Dynamite -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 12:48 PM To: CF-Talk Subject: Re: Query of Queries Ben Nadel wrote: > Do any parts of the query work? Meaning, If you run with just the > first SUM directive does it work? Does it work if you do not do the > "*" in the second SUM? Let's see if we can narrow it down to exactly what is breaking? It is definately this one causing the problem: select SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE FROM contents Both values are numeric (I output #isNumeric(contents.QUANTITY)# and #isNumeric(contents.OPTION_PRICE)# The values are 1 and 16.99 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240361 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query of Queries
Try using the val() function. > -Original Message- > From: Rick Root [mailto:[EMAIL PROTECTED] > Sent: Friday, May 12, 2006 10:53 AM > > I ran this query and got a different error: > > select > SUM((OPTION_PRICE+0)) AS MERCH_CHARGE > FROM contents > > Cannot mix types "VARCHAR" and "BIGINT" in a "+" binary operation > > so it looks like for some reason, coldfusion things > "OPTION_PRICE" is a > varchar, even though the column type in the database is > "DECIMAL(10,2)" This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. A1. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240360 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query of Queries
Rick, What happens when you just do "quantity + option_price" -Mark -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 11:48 AM To: CF-Talk Subject: Re: Query of Queries Ben Nadel wrote: > Do any parts of the query work? Meaning, If you run with just the > first SUM directive does it work? Does it work if you do not do the > "*" in the second SUM? Let's see if we can narrow it down to exactly what is breaking? It is definately this one causing the problem: select SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE FROM contents Both values are numeric (I output #isNumeric(contents.QUANTITY)# and #isNumeric(contents.OPTION_PRICE)# The values are 1 and 16.99 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240359 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries
I ran this query and got a different error: select SUM((OPTION_PRICE+0)) AS MERCH_CHARGE FROM contents Cannot mix types "VARCHAR" and "BIGINT" in a "+" binary operation so it looks like for some reason, coldfusion things "OPTION_PRICE" is a varchar, even though the column type in the database is "DECIMAL(10,2)" Rick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240358 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries
Ben Nadel wrote: > Do any parts of the query work? Meaning, If you run with just the first SUM > directive does it work? Does it work if you do not do the "*" in the second > SUM? Let's see if we can narrow it down to exactly what is breaking? It is definately this one causing the problem: select SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE FROM contents Both values are numeric (I output #isNumeric(contents.QUANTITY)# and #isNumeric(contents.OPTION_PRICE)# The values are 1 and 16.99 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240357 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries
Total stab in the dark but maybe you could try changing the alias for QUANTITY so it's not the same name as the column name itself? Maybe that's messing things up somehow. -- Josh - Original Message - From: "Rick Root" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Friday, May 12, 2006 8:47 AM Subject: Re: Query of Queries > Anyone else got any ideas on this? "contents" is a regular query result > set, not anything made by QueryNew()... and the columns in question are > both numeric columns (quantity is an integer and option_price is a > decimal(10,2)... mysql database) > > Rick > > Rick Root wrote: >> I'm getting the following error: >> >> Query Of Queries runtime error. Cannot apply the binary numeric >> operator [*|/] on a non numeric type >> >> When running the following query of queries: >> >> select >> sum(QUANTITY) AS QUANTITY, >> SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE >> FROM contents >> >> I dumped the contents of the "contents" query and both "QUANTITY" and >> "OPTION_PRICE" contain numeric values. >> >> I've never seen this error before and this code hasn't changed from a >> previous installation of the same code on a different server. >> >> This server is at smarterlinux, it's coldfusion mx 7 7,0,1,116466 > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240352 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query of Queries
Did you try putting Val() around quantity and option_price, just to make sure CF isn't doing something funny? > -Original Message- > From: Rick Root [mailto:[EMAIL PROTECTED] > Sent: Friday, May 12, 2006 9:47 AM > > Anyone else got any ideas on this? "contents" is a regular > query result > set, not anything made by QueryNew()... and the columns in > question are > both numeric columns (quantity is an integer and option_price is a > decimal(10,2)... mysql database) > > Rick > > Rick Root wrote: > > I'm getting the following error: > > > > Query Of Queries runtime error. Cannot apply the > binary numeric > > operator [*|/] on a non numeric type > > > > When running the following query of queries: > > > > select > > sum(QUANTITY) AS QUANTITY, > > SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE > > FROM contents > > > > I dumped the contents of the "contents" query and both > "QUANTITY" and > > "OPTION_PRICE" contain numeric values. > > > > I've never seen this error before and this code hasn't > changed from a > > previous installation of the same code on a different server. > > > > This server is at smarterlinux, it's coldfusion mx 7 7,0,1,116466 This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. A1. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240350 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query of Queries
Do any parts of the query work? Meaning, If you run with just the first SUM directive does it work? Does it work if you do not do the "*" in the second SUM? Let's see if we can narrow it down to exactly what is breaking? ... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 212.691.3477 fax www.nylontechnology.com "You know, like nunchuck skills, bowhunting skills, computer hacking skills... Girls only want boyfriends who have great skills." - Napoleon Dynamite -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 11:47 AM To: CF-Talk Subject: Re: Query of Queries Anyone else got any ideas on this? "contents" is a regular query result set, not anything made by QueryNew()... and the columns in question are both numeric columns (quantity is an integer and option_price is a decimal(10,2)... mysql database) Rick Rick Root wrote: > I'm getting the following error: > > Query Of Queries runtime error. Cannot apply the binary > numeric operator [*|/] on a non numeric type > > When running the following query of queries: > > select > sum(QUANTITY) AS QUANTITY, > SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE > FROM contents > > I dumped the contents of the "contents" query and both "QUANTITY" and > "OPTION_PRICE" contain numeric values. > > I've never seen this error before and this code hasn't changed from a > previous installation of the same code on a different server. > > This server is at smarterlinux, it's coldfusion mx 7 7,0,1,116466 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240347 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries
Anyone else got any ideas on this? "contents" is a regular query result set, not anything made by QueryNew()... and the columns in question are both numeric columns (quantity is an integer and option_price is a decimal(10,2)... mysql database) Rick Rick Root wrote: > I'm getting the following error: > > Query Of Queries runtime error. Cannot apply the binary numeric > operator [*|/] on a non numeric type > > When running the following query of queries: > > select > sum(QUANTITY) AS QUANTITY, > SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE > FROM contents > > I dumped the contents of the "contents" query and both "QUANTITY" and > "OPTION_PRICE" contain numeric values. > > I've never seen this error before and this code hasn't changed from a > previous installation of the same code on a different server. > > This server is at smarterlinux, it's coldfusion mx 7 7,0,1,116466 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240342 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries
Can you send me a full code sample to look at? I'd like to play with it a bit to see what's up. I've been finding a number of (rare) issues with CF tags, functions and combinations of syntax. I'm logging them all to www.blogoffusion.com and this looks like another one to go up there. Thanks > Michael, > > I have actually had problems where the data type caused a problem. I was > building a query based on "score", all of which were of type "Integer"... > When dumping the query, all values were numeric... However when sorting on > the column, it would throw errors about trying to sort on non-numeric > types. > If I stopped sending in the Data types to QueryNew(), worked perfect. > > This is the line that was causing the error. If this line didn't get > called, > the sort worked fine: > > Min( >( > REQUEST.SearchQuery[ "score" ][ REQUEST.SearchQuery.CurrentRow ] + > Ceiling(100 / ArrayLen(arrCriteria)) > ), > 80 > ) /> > > This line only gets called if there is an ArrayLen()... Score always > defaults to zero... I can't see anyway for this to return anything but a > numeric value. Yet, if I comment it out, the sort would work fine. There > was > not code afterwards that would be affected by it (no conditional logic > affected by my commenting out the line). > > And like I said, if I look out the data type passing, works great. > > Seems like a bug to me. > > ... > Ben Nadel > Web Developer > Nylon Technology > 350 7th Avenue > Floor 10 > New York, NY 10001 > 212.691.1134 > 212.691.3477 fax > www.nylontechnology.com > > "You know, like nunchuck skills, bowhunting skills, computer hacking > skills... Girls only want boyfriends who have great skills." > - Napoleon Dynamite > -Original Message- > From: Michael Dinowitz [mailto:[EMAIL PROTECTED] > Sent: Friday, May 12, 2006 9:42 AM > To: CF-Talk > Subject: Re: Query of Queries > > When creating a query using QueryNew() you can add an optional second > attribute to it which will define each column as a specific data type. > This > will avoid the error: > > QueryNew(columnlist, [columntypelist]) > > Michael Dinowitz > Host: House of Fusion >http://www.houseoffusion.com > Publisher: Fusion Authority >http://www.fusionauthority.com > > > > >>I have never seen this specifically, but I have gotten similar errors >>when I try to define a query with QueryNew() sending in data types... >>Are you using >> QueryNew() to define any query that you are queryying? >> >> ... >> Ben Nadel >> Web Developer >> Nylon Technology >> 350 7th Avenue >> Floor 10 >> New York, NY 10001 >> 212.691.1134 >> 212.691.3477 fax >> www.nylontechnology.com >> >> "You know, like nunchuck skills, bowhunting skills, computer hacking >> skills... Girls only want boyfriends who have great skills." >> - Napoleon Dynamite >> -Original Message- >> From: Rick Root [mailto:[EMAIL PROTECTED] >> Sent: Friday, May 12, 2006 9:31 AM >> To: CF-Talk >> Subject: Query of Queries >> >> I'm getting the following error: >> >> Query Of Queries runtime error. Cannot apply the binary >> numeric operator [*|/] on a non numeric type >> >> When running the following query of queries: >> >> select >> sum(QUANTITY) AS QUANTITY, >> SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE FROM contents >> >> I dumped the contents of the "contents" query and both "QUANTITY" and >> "OPTION_PRICE" contain numeric values. >> >> I've never seen this error before and this code hasn't changed from a >> previous installation of the same code on a different server. >> >> This server is at smarterlinux, it's coldfusion mx 7 7,0,1,116466 >> >> Rick >> >> >> >> > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240322 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries
Ben Nadel wrote: > I have never seen this specifically, but I have gotten similar errors when I > try to define a query with QueryNew() sending in data types... Are you using > QueryNew() to define any query that you are queryying? Nope. contents is a query returned from a CFC, and both fields are numeric fields. Rick > -Original Message- > From: Rick Root [mailto:[EMAIL PROTECTED] > Sent: Friday, May 12, 2006 9:31 AM > To: CF-Talk > Subject: Query of Queries > > I'm getting the following error: > > Query Of Queries runtime error. Cannot apply the binary numeric > operator [*|/] on a non numeric type > > When running the following query of queries: > > select > sum(QUANTITY) AS QUANTITY, > SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE > FROM contents > > I dumped the contents of the "contents" query and both "QUANTITY" and > "OPTION_PRICE" contain numeric values. > > I've never seen this error before and this code hasn't changed from a > previous installation of the same code on a different server. > > This server is at smarterlinux, it's coldfusion mx 7 7,0,1,116466 > > Rick > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240321 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query of Queries
Michael, I have actually had problems where the data type caused a problem. I was building a query based on "score", all of which were of type "Integer"... When dumping the query, all values were numeric... However when sorting on the column, it would throw errors about trying to sort on non-numeric types. If I stopped sending in the Data types to QueryNew(), worked perfect. This is the line that was causing the error. If this line didn't get called, the sort worked fine: This line only gets called if there is an ArrayLen()... Score always defaults to zero... I can't see anyway for this to return anything but a numeric value. Yet, if I comment it out, the sort would work fine. There was not code afterwards that would be affected by it (no conditional logic affected by my commenting out the line). And like I said, if I look out the data type passing, works great. Seems like a bug to me. ... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 212.691.3477 fax www.nylontechnology.com "You know, like nunchuck skills, bowhunting skills, computer hacking skills... Girls only want boyfriends who have great skills." - Napoleon Dynamite -Original Message- From: Michael Dinowitz [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 9:42 AM To: CF-Talk Subject: Re: Query of Queries When creating a query using QueryNew() you can add an optional second attribute to it which will define each column as a specific data type. This will avoid the error: QueryNew(columnlist, [columntypelist]) Michael Dinowitz Host: House of Fusion http://www.houseoffusion.com Publisher: Fusion Authority http://www.fusionauthority.com >I have never seen this specifically, but I have gotten similar errors >when I try to define a query with QueryNew() sending in data types... >Are you using > QueryNew() to define any query that you are queryying? > > ... > Ben Nadel > Web Developer > Nylon Technology > 350 7th Avenue > Floor 10 > New York, NY 10001 > 212.691.1134 > 212.691.3477 fax > www.nylontechnology.com > > "You know, like nunchuck skills, bowhunting skills, computer hacking > skills... Girls only want boyfriends who have great skills." > - Napoleon Dynamite > -Original Message- > From: Rick Root [mailto:[EMAIL PROTECTED] > Sent: Friday, May 12, 2006 9:31 AM > To: CF-Talk > Subject: Query of Queries > > I'm getting the following error: > > Query Of Queries runtime error. Cannot apply the binary > numeric operator [*|/] on a non numeric type > > When running the following query of queries: > > select > sum(QUANTITY) AS QUANTITY, > SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE FROM contents > > I dumped the contents of the "contents" query and both "QUANTITY" and > "OPTION_PRICE" contain numeric values. > > I've never seen this error before and this code hasn't changed from a > previous installation of the same code on a different server. > > This server is at smarterlinux, it's coldfusion mx 7 7,0,1,116466 > > Rick > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240320 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries
When creating a query using QueryNew() you can add an optional second attribute to it which will define each column as a specific data type. This will avoid the error: QueryNew(columnlist, [columntypelist]) Michael Dinowitz Host: House of Fusion http://www.houseoffusion.com Publisher: Fusion Authority http://www.fusionauthority.com >I have never seen this specifically, but I have gotten similar errors when >I > try to define a query with QueryNew() sending in data types... Are you > using > QueryNew() to define any query that you are queryying? > > ... > Ben Nadel > Web Developer > Nylon Technology > 350 7th Avenue > Floor 10 > New York, NY 10001 > 212.691.1134 > 212.691.3477 fax > www.nylontechnology.com > > "You know, like nunchuck skills, bowhunting skills, computer hacking > skills... Girls only want boyfriends who have great skills." > - Napoleon Dynamite > -Original Message- > From: Rick Root [mailto:[EMAIL PROTECTED] > Sent: Friday, May 12, 2006 9:31 AM > To: CF-Talk > Subject: Query of Queries > > I'm getting the following error: > > Query Of Queries runtime error. Cannot apply the binary numeric > operator [*|/] on a non numeric type > > When running the following query of queries: > > select > sum(QUANTITY) AS QUANTITY, > SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE > FROM contents > > I dumped the contents of the "contents" query and both "QUANTITY" and > "OPTION_PRICE" contain numeric values. > > I've never seen this error before and this code hasn't changed from a > previous installation of the same code on a different server. > > This server is at smarterlinux, it's coldfusion mx 7 7,0,1,116466 > > Rick > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240318 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query of Queries
I have never seen this specifically, but I have gotten similar errors when I try to define a query with QueryNew() sending in data types... Are you using QueryNew() to define any query that you are queryying? ... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 212.691.3477 fax www.nylontechnology.com "You know, like nunchuck skills, bowhunting skills, computer hacking skills... Girls only want boyfriends who have great skills." - Napoleon Dynamite -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 9:31 AM To: CF-Talk Subject: Query of Queries I'm getting the following error: Query Of Queries runtime error. Cannot apply the binary numeric operator [*|/] on a non numeric type When running the following query of queries: select sum(QUANTITY) AS QUANTITY, SUM(QUANTITY*OPTION_PRICE) AS MERCH_CHARGE FROM contents I dumped the contents of the "contents" query and both "QUANTITY" and "OPTION_PRICE" contain numeric values. I've never seen this error before and this code hasn't changed from a previous installation of the same code on a different server. This server is at smarterlinux, it's coldfusion mx 7 7,0,1,116466 Rick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240317 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query of Queries
This usually happens when you don't use CFQUERYPARAM in a QofQ. I don't know why exactly, but if you put that in, the type comparison is generally fixed (in my experience). -ben ... Ben Nadel Web Developer Nylon Technology 350 7th Ave. Suite 1005 New York, NY 10001 212.691.1134 x 14 212.691.3477 fax www.nylontechnology.com Sanders: Lightspeed too slow? Helmet: Yes we'll have to go right to ludacris speed. -Original Message- From: Paul Giesenhagen [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 1:44 PM To: CF-Talk Subject: Query of Queries I don't use QofQ much and am having some troubles that maybe someone can help with. I am building a query called getShipping that populates code, description, rate. I am then running a query of queries checking on the selected rate. SELECT code,description,rate FROM getShipping WHERE code = '#order.shipMethod#' The problem is erroring out on the WHERE code = '#order.shipMethod#' ... even if it has a valid method (ie FEDEXGROUND) it is giving the following error: Query Of Queries runtime error. Unsupported type comparison. 1) does anyone see any problem with this? 2) Is there anotherway of getting a specific row/column value out of a query result set ? Paul Giesenhagen QuillDesign 417-885-1375 http://www.quilldesign.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236750 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query of Queries
By chance is one or more of the possible values of code a number? Mike -Original Message- From: Paul Giesenhagen [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 12:44 PM To: CF-Talk Subject: Query of Queries I don't use QofQ much and am having some troubles that maybe someone can help with. I am building a query called getShipping that populates code, description, rate. I am then running a query of queries checking on the selected rate. SELECT code,description,rate FROM getShipping WHERE code = '#order.shipMethod#' The problem is erroring out on the WHERE code = '#order.shipMethod#' ... even if it has a valid method (ie FEDEXGROUND) it is giving the following error: Query Of Queries runtime error. Unsupported type comparison. 1) does anyone see any problem with this? 2) Is there anotherway of getting a specific row/column value out of a query result set ? Paul Giesenhagen QuillDesign 417-885-1375 http://www.quilldesign.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236724 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries and GROUP - problem
>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. > Turned out it was an issue with CF5. The server was a W2K server with IIS 5 and CF5. That seems to be the reason why several things that worked fine on my 6.0 MX developer edition were throwing an error when uploaded to the CF5 server. Roberto ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234957 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries and GROUP - problem
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 to sort on reserved words for query of queries. > 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 the other. :0) > Doug :0) >>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: s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234317 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries and GROUP - problem
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 CFoutput, in CFoutput alone, etc), and the only way it worked was to have all of them aliased. Like I said, the output of a ColumnList command yielded different values for each column on my desktop and on the server, but the aliases were interpreted exactly the same by both development and production servers (still waiting on the specs of the production one). Why the columns were being interpreted differently by DEV and PRO, that would be good to know. Roberto ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234314 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
