Re: Query of queries question
> I have a query which returns the result set like below. The column names are > like 1990, 1991, 1992 and there are certain amount > for each year > > 1990 1991 1992 > 1000 5000 6000 > 2000 2000 3000 This doesn't address your question directly, but again this is a very poor database design. I strongly recommend that you learn how to design a normalized database schema. If you design your schema well, you won't run into the problems you're describing. Of course, if you're not responsible for the database design, or if you're building a query that presents a denormalized recordset from a normalized schema, feel free to disregard this. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334432 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
>Ah, right QoQ can be bitchy. Can't you alias the column names in the initial >query? > >SELECT my1990 = [1990], my1991 = [1991] >or >SELECT [1990] as my1990 etc etc > >That way can reference them without any issues. > >P.S. 1990 and the like are absolutely horrible column names. > >On Wed, Jun 9, 2010 at 11:41 AM, fun and learning >wrote: > >> Thanks you all for the replies. Prepending a variable before the year helped me ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334426 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
If you can't rename the columns as someone else suggested, you can change your select to select 1990 as y1990, 1991 as y1991, etc.. then in your query of querys you do select sum(y1990) as sum1990 On Wed, Jun 9, 2010 at 8:27 AM, fun and learning wrote: > > HI All - > > I have a query which returns the result set like below. The column names are > like 1990, 1991, 1992 and there are certain amount for each year > > 1990 1991 1992 > 1000 5000 6000 > 2000 2000 3000 > > I have a query of query which finds the sum of the amounts for year 1990, and > I use the query like Select SUM(1990), it returns the sum of 1990+1990. How > to make sure that 1990 is recognized as a column instead as a number. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334423 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
Ah, right QoQ can be bitchy. Can't you alias the column names in the initial query? SELECT my1990 = [1990], my1991 = [1991] or SELECT [1990] as my1990 etc etc That way can reference them without any issues. P.S. 1990 and the like are absolutely horrible column names. On Wed, Jun 9, 2010 at 11:41 AM, fun and learning wrote: > > >Your first issue is that that is not a good db practice naming columns as > >numbers. If you put a prefix of "y" in front of the column name in your > >table (or whatever you chose, i.e y1990, y1991, y1992) it wouldn't have > this > >issue, it would force your SQL to actual look for a table column rather > than > >just summing the number you entered. In some databases you can force the > >issue by surrounding the column header in quotations (Sum('1990')) but you > >cannot count on that, you are better off renaming your column headers as I > >said earlier. > > > >Jim Eisenhauer > > > > > >On Wed, Jun 9, 2010 at 8:27 AM, fun and learning >wrote: > > > >> > > I get the following error when I use quotes > Query Of Queries syntax error. > Encountered "SUM. Lexical error at line 0, column 0. Encountered: "\"" > (34), after : " > > and the following error when I use brackets > Query Of Queries syntax error. > Encountered "SUM ( [. Incorrect Select List > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334414 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
>Your first issue is that that is not a good db practice naming columns as >numbers. If you put a prefix of "y" in front of the column name in your >table (or whatever you chose, i.e y1990, y1991, y1992) it wouldn't have this >issue, it would force your SQL to actual look for a table column rather than >just summing the number you entered. In some databases you can force the >issue by surrounding the column header in quotations (Sum('1990')) but you >cannot count on that, you are better off renaming your column headers as I >said earlier. > >Jim Eisenhauer > > >On Wed, Jun 9, 2010 at 8:27 AM, fun and learning >wrote: > >> I get the following error when I use quotes Query Of Queries syntax error. Encountered "SUM. Lexical error at line 0, column 0. Encountered: "\"" (34), after : " and the following error when I use brackets Query Of Queries syntax error. Encountered "SUM ( [. Incorrect Select List ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334413 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
Your first issue is that that is not a good db practice naming columns as numbers. If you put a prefix of "y" in front of the column name in your table (or whatever you chose, i.e y1990, y1991, y1992) it wouldn't have this issue, it would force your SQL to actual look for a table column rather than just summing the number you entered. In some databases you can force the issue by surrounding the column header in quotations (Sum('1990')) but you cannot count on that, you are better off renaming your column headers as I said earlier. Jim Eisenhauer On Wed, Jun 9, 2010 at 8:27 AM, fun and learning wrote: > > HI All - > > I have a query which returns the result set like below. The column names > are like 1990, 1991, 1992 and there are certain amount for each year > > 1990 1991 1992 > 1000 5000 6000 > 2000 2000 3000 > > I have a query of query which finds the sum of the amounts for year 1990, > and I use the query like Select SUM(1990), it returns the sum of 1990+1990. > How to make sure that 1990 is recognized as a column instead as a number. > > Thanks > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334411 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
SUM([1990]) Just use square brackets around column name. On Wed, Jun 9, 2010 at 11:27 AM, fun and learning wrote: > > HI All - > > I have a query which returns the result set like below. The column names > are like 1990, 1991, 1992 and there are certain amount for each year > > 1990 1991 1992 > 1000 5000 6000 > 2000 2000 3000 > > I have a query of query which finds the sum of the amounts for year 1990, > and I use the query like Select SUM(1990), it returns the sum of 1990+1990. > How to make sure that 1990 is recognized as a column instead as a number. > > Thanks > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334410 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of queries question
Should be double quotes I think Select SUM("1990") Some DBs use brackets too I think sum([1990]) Not sure how it would work undr the hood with query of query, can't you pull this from the db directly? On Wed, 2010-06-09 at 11:27 -0400, fun and learning wrote: > HI All - > > I have a query which returns the result set like below. The column names are > like 1990, 1991, 1992 and there are certain amount for each year > > 1990 1991 1992 > 1000 5000 6000 > 2000 2000 3000 > > I have a query of query which finds the sum of the amounts for year 1990, and > I use the query like Select SUM(1990), it returns the sum of 1990+1990. How > to make sure that 1990 is recognized as a column instead as a number. > > Thanks > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334409 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: query of queries question
I would cache your initial result, and build a set of filters that are applied to it each time the user updates their filter. So, you would have cachedQuery as your initial base search, then filteredQuery that you would not cache but would always be the result of a QoQ of your cached query with updated filters applied against it. Chris Peterson Gainey IT Adobe Certified Advanced Coldfusion Developer ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282539 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query of Queries Question
Tried thatI did finally find it in the docs. You can actually specify your own escape character. So it's like this: SELECT * FROM whatever WHERE what LIKE '%95\%%' ESCAPE '\' This will find anything that has 95% in the resultset 'whatever', column 'what'. It doesn't matter what character you use. Of course, you want to make sure that character is not in your text you are queryingso I ended up doing this: SELECT * FROM whatever WHERE what LIKE '%95#chr(9)#%%' ESCAPE '#chr(9)#' Ascii Character 9 is the TAB character and in my circumstance it works well because I'm comparing URLs and they don't contain tab characters. Hope this helps someone else who runs into the same thing! Dave -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Monday, February 26, 2007 10:36 AM To: CF-Talk Subject: RE: Query of Queries Question This is just a guess, but you can try putting either a "\" or a "'" before the characters that needs to be escaped?? LIKE 'foo\%bar' Or maybe LIKE 'foo'%bar' The single quote is only a guess because single quote escaped another single quote. ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270716 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query of Queries Question
This is just a guess, but you can try putting either a "\" or a "'" before the characters that needs to be escaped?? LIKE 'foo\%bar' Or maybe LIKE 'foo'%bar' The single quote is only a guess because single quote escaped another single quote. .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Dave Phillips [mailto:[EMAIL PROTECTED] Sent: Monday, February 26, 2007 11:32 AM To: CF-Talk Subject: Query of Queries Question Hi, Anyone know how to force a LIKE comparison in a QofQ query to look for literals of the following special characters: % _ [ ] ^ All of the above are characters used in a LIKE comparison, but my string has several of them in it, so I need to 'escape' them somehow. I'm on MX 6.1. Thanks! Sincerely, Dave Phillips President WebTech Staffing, LLC [EMAIL PROTECTED] (817) 473-2119 ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270676 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
You could also add a RowNumber column to your query: Then, do: SELECT * FROM qSomeQuery WHERE RowNumber >= 21 AND RowNumber < 41 ORDERY BY LastName Not sure what the performance hit on doing this would be. Jon On 1/5/07, Dave Phillips <[EMAIL PROTECTED]> wrote: > Does anyone know if it's possible to extract a 'subset' of records in a query > utilizing Query of Queries? Basically I want to do something like this: > > > SELECT * > FROM qSomeQuery > WHERE currentRow >= 21 AND currentRow < 41 > ORDERY BY LastName > > > Of course, this doesn't work because currentrow does not specify the row > number inside of a Query, only when you are working with the query results > directly (like qSomeQuery.currentRow). > > Anyone know how this is possible? The only thing I can think of doing is > something like this: > > >*** In here I would build a new query with only the rows available as I > loop through with functions like queryAddRow() and querySetCell(). > > > This idea would not be very efficient, so I'm looking for an alternative > method. > > Thanks for your help in advance! > > Dave > > ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265888 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries question
Or even easier, without the loop. SELECT * FROM qMyQuery WHERE id IN (#list_id#) -- Russ -Original Message- From: Terry Sta. Maria [mailto:[EMAIL PROTECTED] Sent: 05 January 2007 20:52 To: CF-Talk Subject: Re: Query of Queries question You don't necessarily have to create a query from scratch. Instead, if there is a column (with a simple datatype) that uniquely identifies each row, this can be done with a list and a query of queries. If qMyQuery.id is such a column, then you can get those all into a list: Then you can do your query of queries with this list: SELECT * FROM qMyQuery WHERE id IN (#list_id#) If your id column isn't numeric, you can loop through the list creating "LIKE" clauses for string, or similar comparisons for other simple data types. Hope this helps. ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265875 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
You don't necessarily have to create a query from scratch. Instead, if there is a column (with a simple datatype) that uniquely identifies each row, this can be done with a list and a query of queries. If qMyQuery.id is such a column, then you can get those all into a list: Then you can do your query of queries with this list: SELECT * FROM qMyQuery WHERE id IN (#list_id#) If your id column isn't numeric, you can loop through the list creating "LIKE" clauses for string, or similar comparisons for other simple data types. Hope this helps. ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265872 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries question
The query is not coming from a DB. It is actually coming from a CFSEARCH tag, so unfortunately, I can't use this method. >not sure what DB you're using.. but with Oracle you can do this in a subquery >select * from >( >select rownum as rn, n.* >From navmenu n >) >where rn >= 5 and rn <= 7 > > > >On 1/5/07, Dave Phillips <[EMAIL PROTECTED]> wrote: >> ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265870 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
not sure what DB you're using.. but with Oracle you can do this in a subquery select * from ( select rownum as rn, n.* >From navmenu n ) where rn >= 5 and rn <= 7 On 1/5/07, Dave Phillips <[EMAIL PROTECTED]> wrote: > Does anyone know if it's possible to extract a 'subset' of records in a query > utilizing Query of Queries? Basically I want to do something like this: > > > SELECT * > FROM qSomeQuery > WHERE currentRow >= 21 AND currentRow < 41 > ORDERY BY LastName > > > Of course, this doesn't work because currentrow does not specify the row > number inside of a Query, only when you are working with the query results > directly (like qSomeQuery.currentRow). > > Anyone know how this is possible? The only thing I can think of doing is > something like this: > > >*** In here I would build a new query with only the rows available as I > loop through with functions like queryAddRow() and querySetCell(). > > > This idea would not be very efficient, so I'm looking for an alternative > method. > > Thanks for your help in advance! > > Dave > > ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265817 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query of Queries question
Does the result /have/ to be a query? Could you make a structure instead, using your cfoutput method? That should be pretty quick. On 1/5/07, Dave Phillips <[EMAIL PROTECTED]> wrote: > Does anyone know if it's possible to extract a 'subset' of records in a query > utilizing Query of Queries? Basically I want to do something like this: > > > SELECT * > FROM qSomeQuery > WHERE currentRow >= 21 AND currentRow < 41 > ORDERY BY LastName > > > Of course, this doesn't work because currentrow does not specify the row > number inside of a Query, only when you are working with the query results > directly (like qSomeQuery.currentRow). > > Anyone know how this is possible? The only thing I can think of doing is > something like this: > > >*** In here I would build a new query with only the rows available as I > loop through with functions like queryAddRow() and querySetCell(). > > > This idea would not be very efficient, so I'm looking for an alternative > method. > > Thanks for your help in advance! > > Dave > > ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265815 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of queries 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