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: Query of queries question
Should be double quotes I think Select SUM(1990) Some DBs use brackets too I think sum([1990]) Not sure how it would work undr the hood with query of query, can't you pull this from the db directly? On Wed, 2010-06-09 at 11:27 -0400, fun and learning wrote: HI All - I have a query which returns the result set like below. The column names are like 1990, 1991, 1992 and there are certain amount for each year 1990 1991 1992 1000 5000 6000 2000 2000 3000 I have a query of query which finds the sum of the amounts for year 1990, and I use the query like Select SUM(1990), it returns the sum of 1990+1990. How to make sure that 1990 is recognized as a column instead as a number. Thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334409 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
SUM([1990]) Just use square brackets around column name. On Wed, Jun 9, 2010 at 11:27 AM, fun and learning funandlrnn...@gmail.comwrote: 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
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 funandlrnn...@gmail.comwrote:
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
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
funandlrnn...@gmail.comwrote:
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
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
funandlrnn...@gmail.comwrote:
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 funandlrnn...@gmail.com
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
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 funandlrnn...@gmail.com 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 funandlrnn...@gmail.comwrote: 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
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
I would cache your initial result, and build a set of filters that are applied to it each time the user updates their filter. So, you would have cachedQuery as your initial base search, then filteredQuery that you would not cache but would always be the result of a QoQ of your cached query with updated filters applied against it. Chris Peterson Gainey IT Adobe Certified Advanced Coldfusion Developer ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282539 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
query of queries question
I've not really needed to use a query of queries before, so here's a stupid question to be sure it's what I need The first set of query results comes from a search form. After the results are returned and displayed, I need to be able to refine that search based on a specific criteria. Filter your results based on When the filtered set is returned, I need to be able to apply yet another filter to refine the results even more and return a final set of records. OK, so the question - you'd cache the first query results. Then run your next filter, cache those results, and then run your final filter to return the final records - correct? Or is there a better way to do this? ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282530 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 Question
Hi, Anyone know how to force a LIKE comparison in a QofQ query to look for literals of the following special characters: % _ [ ] ^ All of the above are characters used in a LIKE comparison, but my string has several of them in it, so I need to 'escape' them somehow. I'm on MX 6.1. Thanks! Sincerely, Dave Phillips President WebTech Staffing, LLC [EMAIL PROTECTED] (817) 473-2119 ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270675 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries Question
This is just a guess, but you can try putting either a \ or a ' before the characters that needs to be escaped?? LIKE 'foo\%bar' Or maybe LIKE 'foo'%bar' The single quote is only a guess because single quote escaped another single quote. .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Dave Phillips [mailto:[EMAIL PROTECTED] Sent: Monday, February 26, 2007 11:32 AM To: CF-Talk Subject: Query of Queries Question Hi, Anyone know how to force a LIKE comparison in a QofQ query to look for literals of the following special characters: % _ [ ] ^ All of the above are characters used in a LIKE comparison, but my string has several of them in it, so I need to 'escape' them somehow. I'm on MX 6.1. Thanks! Sincerely, Dave Phillips President WebTech Staffing, LLC [EMAIL PROTECTED] (817) 473-2119 ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270676 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries Question
Tried thatI did finally find it in the docs. You can actually specify your own escape character. So it's like this: SELECT * FROM whatever WHERE what LIKE '%95\%%' ESCAPE '\' This will find anything that has 95% in the resultset 'whatever', column 'what'. It doesn't matter what character you use. Of course, you want to make sure that character is not in your text you are queryingso I ended up doing this: SELECT * FROM whatever WHERE what LIKE '%95#chr(9)#%%' ESCAPE '#chr(9)#' Ascii Character 9 is the TAB character and in my circumstance it works well because I'm comparing URLs and they don't contain tab characters. Hope this helps someone else who runs into the same thing! Dave -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Monday, February 26, 2007 10:36 AM To: CF-Talk Subject: RE: Query of Queries Question This is just a guess, but you can try putting either a \ or a ' before the characters that needs to be escaped?? LIKE 'foo\%bar' Or maybe LIKE 'foo'%bar' The single quote is only a guess because single quote escaped another single quote. ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270716 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Query of Queries question
Does anyone know if it's possible to extract a 'subset' of records in a query utilizing Query of Queries? Basically I want to do something like this: cfquery name=qMyQuery dbtype=query SELECT * FROM qSomeQuery WHERE currentRow = 21 AND currentRow 41 ORDERY BY LastName /cfquery Of course, this doesn't work because currentrow does not specify the row number inside of a Query, only when you are working with the query results directly (like qSomeQuery.currentRow). Anyone know how this is possible? The only thing I can think of doing is something like this: cfoutput name=qSomeQuery startRow=21 maxrows=40 *** In here I would build a new query with only the rows available as I loop through with functions like queryAddRow() and querySetCell(). /cfoutput This idea would not be very efficient, so I'm looking for an alternative method. Thanks for your help in advance! Dave ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265813 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries question
Does the result /have/ to be a query? Could you make a structure instead, using your cfoutput method? That should be pretty quick. On 1/5/07, Dave Phillips [EMAIL PROTECTED] wrote: Does anyone know if it's possible to extract a 'subset' of records in a query utilizing Query of Queries? Basically I want to do something like this: cfquery name=qMyQuery dbtype=query SELECT * FROM qSomeQuery WHERE currentRow = 21 AND currentRow 41 ORDERY BY LastName /cfquery Of course, this doesn't work because currentrow does not specify the row number inside of a Query, only when you are working with the query results directly (like qSomeQuery.currentRow). Anyone know how this is possible? The only thing I can think of doing is something like this: cfoutput name=qSomeQuery startRow=21 maxrows=40 *** In here I would build a new query with only the rows available as I loop through with functions like queryAddRow() and querySetCell(). /cfoutput This idea would not be very efficient, so I'm looking for an alternative method. Thanks for your help in advance! Dave ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265815 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
not sure what DB you're using.. but with Oracle you can do this in a subquery select * from ( select rownum as rn, n.* From navmenu n ) where rn = 5 and rn = 7 On 1/5/07, Dave Phillips [EMAIL PROTECTED] wrote: Does anyone know if it's possible to extract a 'subset' of records in a query utilizing Query of Queries? Basically I want to do something like this: cfquery name=qMyQuery dbtype=query SELECT * FROM qSomeQuery WHERE currentRow = 21 AND currentRow 41 ORDERY BY LastName /cfquery Of course, this doesn't work because currentrow does not specify the row number inside of a Query, only when you are working with the query results directly (like qSomeQuery.currentRow). Anyone know how this is possible? The only thing I can think of doing is something like this: cfoutput name=qSomeQuery startRow=21 maxrows=40 *** In here I would build a new query with only the rows available as I loop through with functions like queryAddRow() and querySetCell(). /cfoutput This idea would not be very efficient, so I'm looking for an alternative method. Thanks for your help in advance! Dave ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265817 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
The query is not coming from a DB. It is actually coming from a CFSEARCH tag, so unfortunately, I can't use this method. not sure what DB you're using.. but with Oracle you can do this in a subquery select * from ( select rownum as rn, n.* From navmenu n ) where rn = 5 and rn = 7 On 1/5/07, Dave Phillips [EMAIL PROTECTED] wrote: ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265870 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
You don't necessarily have to create a query from scratch. Instead, if there is a column (with a simple datatype) that uniquely identifies each row, this can be done with a list and a query of queries. If qMyQuery.id is such a column, then you can get those all into a list: cfset list_id = '' cfloop query=qMyQuery cfif qMyQuery.currentRow gte int_rowStart and qMyQuery.currentRow lte int_rowEnd cfset list_id = listAppend(list_id, qMyQuery.id) cfelseif qMyQuery.currentRow gt int_rowEnd cfbreak /cfif /cfloop Then you can do your query of queries with this list: cfquery dbtype=query SELECT * FROM qMyQuery WHERE id IN (#list_id#) /cfquery If your id column isn't numeric, you can loop through the list creating LIKE clauses for string, or similar comparisons for other simple data types. Hope this helps. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265872 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query of Queries question
Or even easier, without the loop. cfset list_id = ValueList(qMyQuery.id) cfquery dbtype=query SELECT * FROM qMyQuery WHERE id IN (#list_id#) /cfquery -- Russ -Original Message- From: Terry Sta. Maria [mailto:[EMAIL PROTECTED] Sent: 05 January 2007 20:52 To: CF-Talk Subject: Re: Query of Queries question You don't necessarily have to create a query from scratch. Instead, if there is a column (with a simple datatype) that uniquely identifies each row, this can be done with a list and a query of queries. If qMyQuery.id is such a column, then you can get those all into a list: cfset list_id = '' cfloop query=qMyQuery cfif qMyQuery.currentRow gte int_rowStart and qMyQuery.currentRow lte int_rowEnd cfset list_id = listAppend(list_id, qMyQuery.id) cfelseif qMyQuery.currentRow gt int_rowEnd cfbreak /cfif /cfloop Then you can do your query of queries with this list: cfquery dbtype=query SELECT * FROM qMyQuery WHERE id IN (#list_id#) /cfquery If your id column isn't numeric, you can loop through the list creating LIKE clauses for string, or similar comparisons for other simple data types. Hope this helps. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265875 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
You could also add a RowNumber column to your query: cfset QueryAddColumn(qSomeQuery, RowNumber) cfloop from=1 to=qSomeQuery.RecordCount index=RowNumber cfset QuerySetCell(qSomeQuery, RowNumber, RowNumber, RowNumber) /cfloop Then, do: cfquery name=qMyQuery dbtype=query SELECT * FROM qSomeQuery WHERE RowNumber = 21 AND RowNumber 41 ORDERY BY LastName /cfquery Not sure what the performance hit on doing this would be. Jon On 1/5/07, Dave Phillips [EMAIL PROTECTED] wrote: Does anyone know if it's possible to extract a 'subset' of records in a query utilizing Query of Queries? Basically I want to do something like this: cfquery name=qMyQuery dbtype=query SELECT * FROM qSomeQuery WHERE currentRow = 21 AND currentRow 41 ORDERY BY LastName /cfquery Of course, this doesn't work because currentrow does not specify the row number inside of a Query, only when you are working with the query results directly (like qSomeQuery.currentRow). Anyone know how this is possible? The only thing I can think of doing is something like this: cfoutput name=qSomeQuery startRow=21 maxrows=40 *** In here I would build a new query with only the rows available as I loop through with functions like queryAddRow() and querySetCell(). /cfoutput This idea would not be very efficient, so I'm looking for an alternative method. Thanks for your help in advance! Dave ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265888 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Query of queries question
Hi all, So far I dont have CF 5 but I really could use the query of queries feature, along with some graphing :) Anyways I was wondering how many records would be too many in your original query. Im assuming this is a RAM issue. The app I would be using this on is a marketing system. So basically I want my original query to return all the customers leads then use query of queries to filter the list, do some demographic targeting. For example query for all the males 18-24. This original query could be large, lets say 100,000 records. Could CF handle it? How about 1,000,000 records in the first query? If anyone has any ideas on these limits, I'd love to hear them. Thanks much, Jeff ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query of queries question
I don't think you can really measure it... It depends not necessarily on how many records you select, but the amount of data you are using, and how much RAM you have. I did a Query of a Query on a 20mb table(about 140,000 records) and CF grabbed 20 mb of RAM for a sec, and then free'd it up. +++ Pete Freitag ([EMAIL PROTECTED]) CFDEV.COM ColdFusion Developer Resources http://www.cfdev.com/ -Original Message- From: Jeff Green [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 28, 2001 5:52 PM To: CF-Talk Subject: Query of queries question Hi all, So far I dont have CF 5 but I really could use the query of queries feature, along with some graphing :) Anyways I was wondering how many records would be too many in your original query. Im assuming this is a RAM issue. The app I would be using this on is a marketing system. So basically I want my original query to return all the customers leads then use query of queries to filter the list, do some demographic targeting. For example query for all the males 18-24. This original query could be large, lets say 100,000 records. Could CF handle it? How about 1,000,000 records in the first query? If anyone has any ideas on these limits, I'd love to hear them. Thanks much, Jeff ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
