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
Query of Queries Error
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:352765 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
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:346455 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
On Wed, Jul 14, 2010 at 10:54 AM, DURETTE, STEVEN J (ATTASIAIT) wrote: > Correct me if I'm wrong (please) but isn't a query already a Struct of Arrays? It is, but you can really only use that to select data based on a column name and row number. Typically, if I were to convert a query to a struct and then use that struct, the key of the struct might be something like the PK of the data, such as userID. This means you could look up a value in the struct like this: #myStruct[variables.userID].firstName# If you leave your resultset as a query, you cannot look up an individual row without knowing it's row number. Which makes "it's a struct of arrays" pretty useless if all you know is the user's ID. -Cameron -- Cameron Childress Sumo Consulting Inc http://www.sumoc.com --- cell: 678.637.5072 aim: cameroncf email: camer...@gmail. ~| 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:335372 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
Michael,
Any chance you can give us a real example and some more detail so we may try
to give you the best solution?
On Wed, Jul 14, 2010 at 10:54 AM, DURETTE, STEVEN J (ATTASIAIT) <
sd1...@att.com> wrote:
>
> Correct me if I'm wrong (please) but isn't a query already a Struct of
> Arrays?
>
> I mean the following is totally valid:
>
> Table user_list (user_fname, user_lname) {shown in comma delimited format}
> Cameron, Childress
> Michael, Dinowitz
> Steve, Durette
>
>
> Select user_id, user_name
> From user_list;
>
>
>
> #Variables.myUserList["user_fname"][2] #
>
>
> Would output: Michael
>
> So after the query is loaded, you can just use the data. No copying no
> extra query, just direct access.
>
> Steve
>
> -Original Message-
> From: Cameron Childress [mailto:camer...@gmail.com]
> Sent: Wednesday, July 14, 2010 10:27 AM
> To: cf-talk
> Subject: Re: struct lookup vs. query of queries
>
>
> On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz
> wrote:
> > I've got a loop which will need to look up a piece of data on each
> > iteration. The data is standardized so there is really just one call
> > to the database (outside the loop). I can either do a query of queries
> > on each iteration or I can turn the query into a structure and do a
> > structure lookup (structkeyexists, etc) on each iteration. I'm
> > assuming that the struct lookup will be faster/more efficient even
> > after having to turn the query into a structure.
> > Anyone have an opinion on this?
>
> I virtually always choose to use a struct in these cases. There are
> very few cases where I have seen an argument to use QoQ for virtually
> anything. QoQ is typically much slower than structs, assuming you are
> using alot of values from the query. Sometimes it can even be faster
> to go back to the DB each time rather than QoQ. Seriously.
>
> Depending on the use case, I like to convert the Query to a struct and
> then cache that struct either in a shared scope, or using CF9's
> EHCache caching mechanisms. You can wrap that whole thing up in a
> nice little CFC and it become really quite reusable.
>
> -Cameron
>
> --
> Cameron Childress
> Sumo Consulting Inc
> http://www.sumoc.com
> ---
> cell: 678.637.5072
> aim: cameroncf
> email: camer...@gmail.
>
>
>
>
~|
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:335368
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: struct lookup vs. query of queries
Correct me if I'm wrong (please) but isn't a query already a Struct of Arrays?
I mean the following is totally valid:
Table user_list (user_fname, user_lname) {shown in comma delimited format}
Cameron, Childress
Michael, Dinowitz
Steve, Durette
Select user_id, user_name
>From user_list;
#Variables.myUserList["user_fname"][2] #
Would output: Michael
So after the query is loaded, you can just use the data. No copying no extra
query, just direct access.
Steve
-Original Message-
From: Cameron Childress [mailto:camer...@gmail.com]
Sent: Wednesday, July 14, 2010 10:27 AM
To: cf-talk
Subject: Re: struct lookup vs. query of queries
On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz
wrote:
> I've got a loop which will need to look up a piece of data on each
> iteration. The data is standardized so there is really just one call
> to the database (outside the loop). I can either do a query of queries
> on each iteration or I can turn the query into a structure and do a
> structure lookup (structkeyexists, etc) on each iteration. I'm
> assuming that the struct lookup will be faster/more efficient even
> after having to turn the query into a structure.
> Anyone have an opinion on this?
I virtually always choose to use a struct in these cases. There are
very few cases where I have seen an argument to use QoQ for virtually
anything. QoQ is typically much slower than structs, assuming you are
using alot of values from the query. Sometimes it can even be faster
to go back to the DB each time rather than QoQ. Seriously.
Depending on the use case, I like to convert the Query to a struct and
then cache that struct either in a shared scope, or using CF9's
EHCache caching mechanisms. You can wrap that whole thing up in a
nice little CFC and it become really quite reusable.
-Cameron
--
Cameron Childress
Sumo Consulting Inc
http://www.sumoc.com
---
cell: 678.637.5072
aim: cameroncf
email: camer...@gmail.
~|
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:335366
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz wrote: > I've got a loop which will need to look up a piece of data on each > iteration. The data is standardized so there is really just one call > to the database (outside the loop). I can either do a query of queries > on each iteration or I can turn the query into a structure and do a > structure lookup (structkeyexists, etc) on each iteration. I'm > assuming that the struct lookup will be faster/more efficient even > after having to turn the query into a structure. > Anyone have an opinion on this? I virtually always choose to use a struct in these cases. There are very few cases where I have seen an argument to use QoQ for virtually anything. QoQ is typically much slower than structs, assuming you are using alot of values from the query. Sometimes it can even be faster to go back to the DB each time rather than QoQ. Seriously. Depending on the use case, I like to convert the Query to a struct and then cache that struct either in a shared scope, or using CF9's EHCache caching mechanisms. You can wrap that whole thing up in a nice little CFC and it become really quite reusable. -Cameron -- Cameron Childress Sumo Consulting Inc http://www.sumoc.com --- cell: 678.637.5072 aim: cameroncf email: camer...@gmail. ~| 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:335362 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
My experience with this is that QoQ are are (really) slow when compared with using structs or arrays. I had a one experience where it brought execution time down from 15-20 seconds using QoQ to 1 or 2 seconds for an array. Generally I create an array of the values I want to look up. EmpArray[1][ID] = tblEmp.Fname EmpArray[2][ID] = tblEmp.Email etc... Even if you use a sub query or a Join to pull these values you are going to hitting the tblEmp table for every record. This way you only hit tblEmp once.. But then again with proper indexes the database route may be faster... so 6 of one or 1/2 dozen of another. G! On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz < mdino...@houseoffusion.com> wrote: > > I've got a loop which will need to look up a piece of data on each > iteration. The data is standardized so there is really just one call > to the database (outside the loop). I can either do a query of queries > on each iteration or I can turn the query into a structure and do a > structure lookup (structkeyexists, etc) on each iteration. I'm > assuming that the struct lookup will be faster/more efficient even > after having to turn the query into a structure. > Anyone have an opinion on this? > > Thanks > > -- > Michael Dinowitz > Lead Author - Adobe Coldfusion Anthology > > http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion > > ~| 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:335359 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
Depending on the size of the lookup table, I like using a struct to hold a reference to the row number, that way I quickly lookup the data in the query. Lookup = structNew(); Lookup[id] = row; then you can do: query.column[Lookup[id]]; -Dan On Tuesday, July 13, 2010, Michael Dinowitz wrote: > > I've got a loop which will need to look up a piece of data on each > iteration. The data is standardized so there is really just one call > to the database (outside the loop). I can either do a query of queries > on each iteration or I can turn the query into a structure and do a > structure lookup (structkeyexists, etc) on each iteration. I'm > assuming that the struct lookup will be faster/more efficient even > after having to turn the query into a structure. > Anyone have an opinion on this? > > Thanks > > -- > Michael Dinowitz > Lead Author - Adobe Coldfusion Anthology > http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion > > ~| 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:335337 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
Can you accomplish the same thing in the db? I would feel pretty confident saying that unless you've got an unusual table it's going to be faster to use the db for you data comparison. On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz < mdino...@houseoffusion.com> wrote: > > I've got a loop which will need to look up a piece of data on each > iteration. The data is standardized so there is really just one call > to the database (outside the loop). I can either do a query of queries > on each iteration or I can turn the query into a structure and do a > structure lookup (structkeyexists, etc) on each iteration. I'm > assuming that the struct lookup will be faster/more efficient even > after having to turn the query into a structure. > Anyone have an opinion on this? > > Thanks > > -- > Michael Dinowitz > Lead Author - Adobe Coldfusion Anthology > > http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion > > ~| 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:335333 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: struct lookup vs. query of queries
I think the size of the query and structure would be an important factor, as well as how many loop iterations are done. Often I lean towards using structures if I need to look up data frequently because the syntax is cleaner. There is also the array method of accessing queries like query.column[row], which might help with something. Whatever you are trying to do might have other solutions as well, such as keeping track of items as they are encountered using separate variables. QoQ doesn't have the reputation of being fast, although neither does turning a query into another data type. Turning a query into another data type also increases the RAM usage since data is stored twice. Either way, my guess would be that the difference in speed would be unnoticeable to the end user, so it might come down to personal preference and ease of maintenance. -Mike Chabot On Tue, Jul 13, 2010 at 12:19 PM, Michael Dinowitz wrote: > > I've got a loop which will need to look up a piece of data on each > iteration. The data is standardized so there is really just one call > to the database (outside the loop). I can either do a query of queries > on each iteration or I can turn the query into a structure and do a > structure lookup (structkeyexists, etc) on each iteration. I'm > assuming that the struct lookup will be faster/more efficient even > after having to turn the query into a structure. > Anyone have an opinion on this? > > Thanks > > -- > Michael Dinowitz > Lead Author - Adobe Coldfusion Anthology > http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion > > ~| 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:335299 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
struct lookup vs. query of queries
I've got a loop which will need to look up a piece of data on each iteration. The data is standardized so there is really just one call to the database (outside the loop). I can either do a query of queries on each iteration or I can turn the query into a structure and do a structure lookup (structkeyexists, etc) on each iteration. I'm assuming that the struct lookup will be faster/more efficient even after having to turn the query into a structure. Anyone have an opinion on this? Thanks -- Michael Dinowitz Lead Author - Adobe Coldfusion Anthology http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion ~| 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:335294 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
Query of queries question
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:334407 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfmodule and query of queries
>>But I can't figure out how to pass the query from one module to another. You can also set your query in the request scope, ie : request.MyQuery The request scope is visible in any module and sub modules. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330492 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfmodule and query of queries
You should really be doing this in a CFC. They're built for stuff like this. Thanks, Eric Cobb http://www.cfgears.com ColdFusion - the most profitable "dead" language I've ever worked with. Thane Sherrington wrote: > Hi all, > > This is probably a stupid question, but my simple mind can't figure > it out. I'm trying to do the following: > > 1)Have a cfmodule that does a large query. > 2)Have a series of cfmodules that do a query of query on the large query > 3)Have a script that calls the cfmodules > > But I can't figure out how to pass the query from one module to > another. I can do it if I use cfinclude, of course, since all the > variables are accessible, but when I try to do it from cfmodule to > cfmodule, the query of query modules can't access the large query > because it's local to the first cfmodule. > > Is there away around this? > > T > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330491 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfmodule and query of queries
Set your query as caller.queryName is one way there are others. You should be using a CFC for this and returning the query probably. -Original Message- From: Thane Sherrington [mailto:th...@computerconnectionltd.com] Sent: Tuesday, February 09, 2010 7:57 AM To: cf-talk Subject: cfmodule and query of queries Hi all, This is probably a stupid question, but my simple mind can't figure it out. I'm trying to do the following: 1)Have a cfmodule that does a large query. 2)Have a series of cfmodules that do a query of query on the large query 3)Have a script that calls the cfmodules But I can't figure out how to pass the query from one module to another. I can do it if I use cfinclude, of course, since all the variables are accessible, but when I try to do it from cfmodule to cfmodule, the query of query modules can't access the large query because it's local to the first cfmodule. Is there away around this? T ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330490 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
cfmodule and query of queries
Hi all, This is probably a stupid question, but my simple mind can't figure it out. I'm trying to do the following: 1)Have a cfmodule that does a large query. 2)Have a series of cfmodules that do a query of query on the large query 3)Have a script that calls the cfmodules But I can't figure out how to pass the query from one module to another. I can do it if I use cfinclude, of course, since all the variables are accessible, but when I try to do it from cfmodule to cfmodule, the query of query modules can't access the large query because it's local to the first cfmodule. Is there away around this? T ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330489 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Passing a query object to a CFC and then using that for a query of queries?
(Admittedly this problem for us occurred when we used a defined LOCAL scope inside the function... ie and then and then in a query of queries... we'd always get errors when trying to do select * from LOCAL.qry Rick On Mon, Jul 20, 2009 at 1:17 PM, Rick Root wrote: > I've never been able to use a multipart identifier for a table in > query of queries until we figured out that it does let you put > brackets in... > > ie... > > FROM [arguments.dataIn] -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324738 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Passing a query object to a CFC and then using that for a query of queries?
I've never been able to use a multipart identifier for a table in query of queries until we figured out that it does let you put brackets in... ie... FROM [arguments.dataIn] -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324736 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Passing a query object to a CFC and then using that for a query of queries?
That was it, Francois. Thanks. On Mon, Jul 20, 2009 at 1:05 PM, Francois Levesque wrote: > > Hi Pete, > You don't need the hash signs around your table name in a QoQ: > > > > > > SELECT * > FROM arguments.dataIn > ORDER BY #arguments.orderby# > > > > > Should work. > > Francois Levesque > http://blog.critical-web.com/ > > > On Mon, Jul 20, 2009 at 1:00 PM, Pete Ruckelshaus >wrote: > > > > > I'm stuck. Actually, I'm probably just being stupid. > > I want to pass a query object into a CFC and then use query of queries to > > manipulate that query object. > > > > Here's the contents of my db.cfc file: > > > > > > > > > > SELECT top 100 * > > FROM person.contact > > > > > > > > > > > > > > > > SELECT * > > FROM #arguments.dataIn# > > ORDER BY #arguments.orderby# > > > > > > > > > > > > And here's the important stuff from my calling page: > > > > > returnvariable="getContacts"> > > > returnvariable="getContacts1"> > > > > > > > > > > The problem is that the CFC is throwing a "Complex object types cannot be > > converted to simple values." error. How do I get the sortDataFromQry > > function to see dataIn as a recordset? > > > > Thanks > > > > Pete > > > > > > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324735 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Passing a query object to a CFC and then using that for a query of queries?
Hi Pete, You don't need the hash signs around your table name in a QoQ: SELECT * FROM arguments.dataIn ORDER BY #arguments.orderby# Should work. Francois Levesque http://blog.critical-web.com/ On Mon, Jul 20, 2009 at 1:00 PM, Pete Ruckelshaus wrote: > > I'm stuck. Actually, I'm probably just being stupid. > I want to pass a query object into a CFC and then use query of queries to > manipulate that query object. > > Here's the contents of my db.cfc file: > > > > > SELECT top 100 * > FROM person.contact > > > > > > > > SELECT * > FROM #arguments.dataIn# > ORDER BY #arguments.orderby# > > > > > > And here's the important stuff from my calling page: > > returnvariable="getContacts"> > returnvariable="getContacts1"> > > > > > The problem is that the CFC is throwing a "Complex object types cannot be > converted to simple values." error. How do I get the sortDataFromQry > function to see dataIn as a recordset? > > Thanks > > Pete > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324732 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Passing a query object to a CFC and then using that for a query of queries?
I'm stuck. Actually, I'm probably just being stupid. I want to pass a query object into a CFC and then use query of queries to manipulate that query object. Here's the contents of my db.cfc file: SELECT top 100 * FROM person.contact SELECT * FROM #arguments.dataIn# ORDER BY #arguments.orderby# And here's the important stuff from my calling page: The problem is that the CFC is throwing a "Complex object types cannot be converted to simple values." error. How do I get the sortDataFromQry function to see dataIn as a recordset? Thanks Pete ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324730 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
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
Query of Queries Functions
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. ~| 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:319856 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Tiered Dot Notation in where clause query of Queries (QoQ)
It thinks a is a table reference. I get this whenever I do QoQs in a function where I var scope LOCAL: FROM LOCAL.myQuery I do what you do, assign to another var first. Would like to hear of a better solution like escaping: FROM [LOCAL.myQuery] Adrian > -Original Message- > From: Justin T [mailto:jus...@treherconsulting.com] > Sent: 23 January 2009 17:43 > To: cf-talk > Subject: Tiered Dot Notation in where clause query of Queries (QoQ) > > Hi, > > I was just wondering what the reason is behind the error I get when I > use dot notation with QoQ in the where clause. > > This will throw an error: > > select * > from a.qry > where a.qry.fkPOCId = 1 > > > This won't: > > > > > select * > from goodQry > where goodQry.fkPOCId = 1 > > > It seems that anything beyond a single dot in the where clause will > cause the QoQ to throw an error. ~| 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:318426 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Tiered Dot Notation in where clause query of Queries (QoQ)
Hi, I was just wondering what the reason is behind the error I get when I use dot notation with QoQ in the where clause. This will throw an error: select * from a.qry where a.qry.fkPOCId = 1 This won't: select * from goodQry where goodQry.fkPOCId = 1 It seems that anything beyond a single dot in the where clause will cause the QoQ to throw an error. ~| 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:318421 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Empty error message in a query of queries
Thanks for the replies Carl and Brad. Turns out you're both on the right track. The query is the same as it loops over various databases and appends the results using the QofQ. There is no chance of the records being the same since they are in different databases and one of the columns is the database name. The "union all" seems like a perfect. It worked immediately. I never knew about this option. Thank you, Brad. Carl, there were 45 columns in the query and not having to specify them all was the solution I opted for. Detailing their types may have worked just as well. Thanks again for your help. Brent ~| 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:312761 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Empty error message in a query of queries
I'm guessing it's a datatype problem. As a guess you could try a union all, since it would keep it from comparing every record in the second query to the first to eliminate dupes. How are these queries being created? Are the columns all identical data types? If you are creating them with querynew() are you specifying data types? ~Brad - Original Message - From: "Carl Von Stetten" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Wednesday, September 17, 2008 4:30 PM Subject: Re: Empty error message in a query of queries > Brent, > > Without seeing the two queries that your are joining together, I can > only guess at the problem. Likely, one of the columns in qReportSummary > doesn't match the datatype of the corresponding column in qSummary, or > you don't have the same number of columns in both queries. > > HTH, > Carl > > Brent Shaub wrote: >> Here's the beginning of the error message for a query of queries: >> >> Message [empty string] >> >> StackTrace java.lang.ClassCastException at >> coldfusion.sql.imq.GenericComparator.compare(Comparator.java:67) at >> coldfusion.sql.imq.TableSorter.compareTo(TableSorter.java:156) at >> coldfusion.sql.imq.TableSorter.compareTo ~| 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:312738 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Empty error message in a query of queries
Brent,
Without seeing the two queries that your are joining together, I can
only guess at the problem. Likely, one of the columns in qReportSummary
doesn't match the datatype of the corresponding column in qSummary, or
you don't have the same number of columns in both queries.
HTH,
Carl
Brent Shaub wrote:
> Here's the beginning of the error message for a query of queries:
>
> Message [empty string]
>
> StackTrace java.lang.ClassCastException at
> coldfusion.sql.imq.GenericComparator.compare(Comparator.java:67) at
> coldfusion.sql.imq.TableSorter.compareTo(TableSorter.java:156) at
> coldfusion.sql.imq.TableSorter.compareTo
>
>
> The query of queries goes:
>
>
> select * from qSummary
> union
> select * from qReportSummary
>
>
> The query has worked for months until today. It continues to work for
> certain report summaries, but not in this one in particular. I'm thinking
> it's because of the data specific to that report. Anyone know of any special
> characters to look out for which might be causing the failure in the QofQ?
> "(" ?
>
> So glad to have this forum to post to so; I'd like to keep my hair,
> Brent
>
>
~|
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:312721
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Empty error message in a query of queries
Here's the beginning of the error message for a query of queries:
Message [empty string]
StackTrace java.lang.ClassCastException at
coldfusion.sql.imq.GenericComparator.compare(Comparator.java:67) at
coldfusion.sql.imq.TableSorter.compareTo(TableSorter.java:156) at
coldfusion.sql.imq.TableSorter.compareTo
The query of queries goes:
select * from qSummary
union
select * from qReportSummary
The query has worked for months until today. It continues to work for certain
report summaries, but not in this one in particular. I'm thinking it's because
of the data specific to that report. Anyone know of any special characters to
look out for which might be causing the failure in the QofQ? "(" ?
So glad to have this forum to post to so; I'd like to keep my hair,
Brent
~|
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:312719
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 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
Query of Queries error
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:312067
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
Query of Queries
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. 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:311911 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
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:311468 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
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
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:310186 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: left join on query of queries
thats ok mike, very clever idea about the temp table as well thanks again! >Sorry, I thought you said "if", not "how". > >As another person suggested, you could use nested loops. Or, you could >insert into a temp SQL table and run the outer join. > >m!ke > >Yes we are aware that they are not supported and we are looking for a >workaround. does anyone have any suggestions? > >thansk ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307024 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: left join on query of queries
This helped me do something like what your asking for. http://tinyurl.com/6r6z92 HTH On Fri, Jun 6, 2008 at 2:14 PM, Dawson, Michael <[EMAIL PROTECTED]> wrote: > Sorry, I thought you said "if", not "how". > > As another person suggested, you could use nested loops. Or, you could > insert into a temp SQL table and run the outer join. > > m!ke > > -Original Message- > From: Richard White [mailto:[EMAIL PROTECTED] > Sent: Friday, June 06, 2008 12:14 PM > To: CF-Talk > Subject: Re: left join on query of queries > > Yes we are aware that they are not supported and we are looking for a > workaround. does anyone have any suggestions? > > thansk > > >>Outer joins are not supported in QoQ. >> >>You will have to come up with a workaround. Search this list for some >>suggestions. >> >>m!ke >> >>hi, >> >>does anyone know how i can achieve a left join in a query of queries >> >>thanks >> >>richard > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306996 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: left join on query of queries
Sorry, I thought you said "if", not "how". As another person suggested, you could use nested loops. Or, you could insert into a temp SQL table and run the outer join. m!ke -Original Message- From: Richard White [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2008 12:14 PM To: CF-Talk Subject: Re: left join on query of queries Yes we are aware that they are not supported and we are looking for a workaround. does anyone have any suggestions? thansk >Outer joins are not supported in QoQ. > >You will have to come up with a workaround. Search this list for some >suggestions. > >m!ke > >hi, > >does anyone know how i can achieve a left join in a query of queries > >thanks > >richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306994 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: left join on query of queries
http://www.google.com/search?hl=en&q=left+join+on+query+of+queries&btnG= Google+Search -Original Message- From: Richard White [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2008 12:14 PM To: CF-Talk Subject: Re: left join on query of queries Yes we are aware that they are not supported and we are looking for a workaround. does anyone have any suggestions? thansk >Outer joins are not supported in QoQ. > >You will have to come up with a workaround. Search this list for some >suggestions. > >m!ke > >hi, > >does anyone know how i can achieve a left join in a query of queries > >thanks > >richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306986 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: left join on query of queries
You'll probably have to loop and use your own conditional logic to build up a separate result set. On Fri, Jun 6, 2008 at 1:14 PM, Richard White <[EMAIL PROTECTED]> wrote: > Yes we are aware that they are not supported and we are looking for a > workaround. does anyone have any suggestions? > > thansk > > > >Outer joins are not supported in QoQ. > > > >You will have to come up with a workaround. Search this list for some > >suggestions. > > > >m!ke > > > >hi, > > > >does anyone know how i can achieve a left join in a query of queries > > > >thanks > > > >richard > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306985 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: left join on query of queries
Yes we are aware that they are not supported and we are looking for a workaround. does anyone have any suggestions? thansk >Outer joins are not supported in QoQ. > >You will have to come up with a workaround. Search this list for some >suggestions. > >m!ke > >hi, > >does anyone know how i can achieve a left join in a query of queries > >thanks > >richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306983 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: left join on query of queries
Outer joins are not supported in QoQ. You will have to come up with a workaround. Search this list for some suggestions. m!ke -Original Message- From: Richard White [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2008 12:02 PM To: CF-Talk Subject: left join on query of queries hi, does anyone know how i can achieve a left join in a query of queries thanks richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306981 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
left join on query of queries
hi, does anyone know how i can achieve a left join in a query of queries thanks richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306980 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: using LIKE in query of queries
thanks james and william, got it sorted. >Try the lower() function - that might work in the QoQ. > >On Sun, Apr 6, 2008 at 3:41 PM, Mike Little <[EMAIL PROTECTED]> wrote: >> hey william, you are right. it does appear to be case sensitive. darn. >> >> >> >Does the product have a product_title of 'Table' or 'table'? >> > >> >I haven't checked in a while, but it might be case-sensitive. > >-- >mxAjax / CFAjax docs and other useful articles: >http://www.bifrost.com.au/blog/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302806 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: using LIKE in query of queries
Try the lower() function - that might work in the QoQ. On Sun, Apr 6, 2008 at 3:41 PM, Mike Little <[EMAIL PROTECTED]> wrote: > hey william, you are right. it does appear to be case sensitive. darn. > > > >Does the product have a product_title of 'Table' or 'table'? > > > >I haven't checked in a while, but it might be case-sensitive. -- mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302802 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: using LIKE in query of queries
hey william, you are right. it does appear to be case sensitive. darn. >Does the product have a product_title of 'Table' or 'table'? > >I haven't checked in a while, but it might be case-sensitive. > >::-Original Message- >:: >::Sent: Sunday, April 06, 2008 12:23 AM >::To: CF-Talk >::Subject: using LIKE in query of queries >:: >::hi guys, >:: >::can anyone see anything wrong with the following query format... >:: >:: >::SELECT * >::FROM qGetAllProducts >::WHERE ( >::(product_title LIKE '%table%') >::OR (product_description LIKE '%table%') >::OR (cat_title LIKE '%table%') >::OR (artist_fname LIKE '%table%') >::OR (artist_lname LIKE '%table%') >::) >::ORDER BY cat_lft, order_key >:: >:: >::the query qGetAllProducts has a recordset of 5 and one of the products has >::a product_title of 'table' - but no records are returned by the qofq? >:: >::mike >:: >:: >:: ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302801 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: using LIKE in query of queries
Does the product have a product_title of 'Table' or 'table'? I haven't checked in a while, but it might be case-sensitive. ::-Original Message- ::From: Mike Little [mailto:[EMAIL PROTECTED] ::Sent: Sunday, April 06, 2008 12:23 AM ::To: CF-Talk ::Subject: using LIKE in query of queries :: ::hi guys, :: ::can anyone see anything wrong with the following query format... :: :: ::SELECT * ::FROM qGetAllProducts ::WHERE ( ::(product_title LIKE '%table%') ::OR (product_description LIKE '%table%') ::OR (cat_title LIKE '%table%') ::OR (artist_fname LIKE '%table%') ::OR (artist_lname LIKE '%table%') ::) ::ORDER BY cat_lft, order_key :: :: ::the query qGetAllProducts has a recordset of 5 and one of the products has ::a product_title of 'table' - but no records are returned by the qofq? :: ::mike :: :: :: ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302800 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
using LIKE in query of queries
hi guys, can anyone see anything wrong with the following query format... SELECT * FROM qGetAllProducts WHERE ( (product_title LIKE '%table%') OR (product_description LIKE '%table%') OR (cat_title LIKE '%table%') OR (artist_fname LIKE '%table%') OR (artist_lname LIKE '%table%') ) ORDER BY cat_lft, order_key the query qGetAllProducts has a recordset of 5 and one of the products has a product_title of 'table' - but no records are returned by the qofq? mike ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302799 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Any reason not to use this query of queries?
I should think so; problems occur with QoQ when it can't figure out the type of data in a column accurately. With the cfdirectory result query, there will be no problems correctly identifying the 'name' column as a string type. Dominic On 27/03/2008, Jim McAtee <[EMAIL PROTECTED]> wrote: > > I have a directory containing dated log files that I need to parse. These > have filenames like: > > reMMDD.log > > So I do a to pull a list of files: > >action="list" > directory="#mylogdir#" > filter="re.log" > name="dir"> > > But I want exclude today's log from being processed. This isn't possible > using cfdirectory's filter, so I figured: > > > > > SELECT * FROM dir > WHERE name <> 're#today#.log' > > > Seems to work. Should this type of QoQ, using the same query name to > effectively delete rows, be safe in most circumstances? > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302149 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Any reason not to use this query of queries?
I have a directory containing dated log files that I need to parse. These have filenames like: reMMDD.log So I do a to pull a list of files: But I want exclude today's log from being processed. This isn't possible using cfdirectory's filter, so I figured: SELECT * FROM dir WHERE name <> 're#today#.log' Seems to work. Should this type of QoQ, using the same query name to effectively delete rows, be safe in most circumstances? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302128 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Hanging Query of Queries
Do you have an example of QoQ that is hanging? CF might have to convert a lot of data for you implicitly. ~| 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:296780 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Hanging Query of Queries
I posted a question on this list a while ago regarding coldfusion momentarily hanging on threads, but the problem persists. Seefusion warns us that the server has more than a set number of requests running, and inspecting the stack trace reveals threads queued. They typically are served eventually, but occasionally the server completetly hangs. What is interesting is that 90% of the time, the thread dump shows that the line it is hanging on is running a query of queries. These do not have cflocks around them. Does anyone have any clues as to why it is so often query of queries that seem to cause hung threads? Are they the cause of the hanging, or alternatively is it something else that is causing the hanging, and it just so happens that query of queries are what is most qualifyable to hang on? They are not intense in the slighest nor returning more than, at max, a few hundred rows. Typically under 10 rows. ~| 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:296598 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
> -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
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? 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:295486 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 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
Query of Queries causing server to hang
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 ~| 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:292226 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
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
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 ~| 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:285768 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 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
