Re: Counting rows IN a query of a query.
> This can't be done can it. I'm sure I've seen this done in Oracle and MS > queries, but not a CF query of a query. I'm slightly confused by your interchagable use of the words "count" and "order". According to the docs, this should work for a count: SELECT count(somecol) AS ROWCOUNT FROM variables.CPT_data WHERE [...] If you are looking to append "rownumber" column to your query, I'm not sure you can do it in the SQL, but rownumber is a natural part of ColdFusion's query return object in the format of query_name.currentRow. Is that what you are looking for? -Cameron -- Cameron Childress Sumo Consulting Inc http://www.sumoc.com --- cell: 678.637.5072 aim: cameroncf email: [EMAIL PROTECTED] ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:204603 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Counting rows IN a query of a query.
This can't be done can it. I'm sure I've seen this done in Oracle and MS
queries, but not a CF query of a query.
But to make sure is there anything I in replace the '?' with in the following
query that would number the rows {1,2,3,...}?
SELECT
*,
'?' AS ROWORDER
FROM
variables.CPT_data
--
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org
Sacramento, CA
"C code. C code run. Run code run. Please!"
- Cynthia Dunning
Confidentiality Notice: This message including any
attachments is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or
distribution is prohibited. If you are not the
intended recipient, please contact the sender and
delete any copies of this message.
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:204596
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Help with Union on a query of a Query
Select * is icky-poo have you tried it by explicitely stating your column to make sure its not Select * ohh icky hurts typing it Adam H On Mon, 17 Jan 2005 17:42:24 -0500, Andrew Tyrone <[EMAIL PROTECTED]> wrote: > One thing I'd like to add is that by using the query functions you are also > able to add the 2nd recordset to the end of the first recordset. If you > were to use a union query and weren't explicity ordering the unioned > recordset, CFMX will use the first field as the index field and merge your > queries and not place the second result set at the end of the first. It all > depends on what you want to happen, so that's not necessarily a bad thing. > > > -Original Message- > > From: Barney Boisvert [mailto:[EMAIL PROTECTED] > > Sent: Monday, January 17, 2005 5:26 PM > > To: CF-Talk > > Subject: Re: Help with Union on a query of a Query > > > > Use the query functions. QofQ has an enormous number of > > issues when dealing with column types, so if you're doing any > > kind of modifications to the queries after you get the back > > from the database, chances are good the QofQ engine will > > complain. Rumor has it that many (or most) of these issues > > will be resolved in Blackstone, thankfully. > > > > cheers, > > barneyb > > > > On Mon, 17 Jan 2005 14:17:41 -0800, Ian Skinner > > <[EMAIL PROTECTED]> wrote: > > > I have a fairly complicated process, where I have to build > > up a query from several different sources. I have got it all > > working, except the last step where I have to add the latest > > record to the end of the newest record. I am trying to do > > this with a union in a query-of-a-query query block. I am > > getting the following error. > > > > > > Query Of Queries runtime error. > > > All resulting columns of queries in a SELECT statement > > containing a UNION operator must have corresponding types. > > > Columns with index number equal "3" have different types > > (OTHER, VARCHAR). > > > > > > Here is the code. > > > > > > SELECT > > > * > > > FROM > > > Data > > > > > > UNION > > > > > > SELECT > > > * > > > FROM > > > CurData > > > > > > > > > I have debugged the process so I know that I have all the > > same columns in both querries. Is there an easy fix to this, > > or is it better if I just start using the query functions to > > add data to the final query object. > > ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190856 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Help with Union on a query of a Query
One thing I'd like to add is that by using the query functions you are also able to add the 2nd recordset to the end of the first recordset. If you were to use a union query and weren't explicity ordering the unioned recordset, CFMX will use the first field as the index field and merge your queries and not place the second result set at the end of the first. It all depends on what you want to happen, so that's not necessarily a bad thing. > -Original Message- > From: Barney Boisvert [mailto:[EMAIL PROTECTED] > Sent: Monday, January 17, 2005 5:26 PM > To: CF-Talk > Subject: Re: Help with Union on a query of a Query > > Use the query functions. QofQ has an enormous number of > issues when dealing with column types, so if you're doing any > kind of modifications to the queries after you get the back > from the database, chances are good the QofQ engine will > complain. Rumor has it that many (or most) of these issues > will be resolved in Blackstone, thankfully. > > cheers, > barneyb > > On Mon, 17 Jan 2005 14:17:41 -0800, Ian Skinner > <[EMAIL PROTECTED]> wrote: > > I have a fairly complicated process, where I have to build > up a query from several different sources. I have got it all > working, except the last step where I have to add the latest > record to the end of the newest record. I am trying to do > this with a union in a query-of-a-query query block. I am > getting the following error. > > > > Query Of Queries runtime error. > > All resulting columns of queries in a SELECT statement > containing a UNION operator must have corresponding types. > > Columns with index number equal "3" have different types > (OTHER, VARCHAR). > > > > Here is the code. > > > > SELECT > > * > > FROM > > Data > > > > UNION > > > > SELECT > > * > > FROM > > CurData > > > > > > I have debugged the process so I know that I have all the > same columns in both querries. Is there an easy fix to this, > or is it better if I just start using the query functions to > add data to the final query object. ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190854 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Help with Union on a query of a Query
Use the query functions. QofQ has an enormous number of issues when dealing with column types, so if you're doing any kind of modifications to the queries after you get the back from the database, chances are good the QofQ engine will complain. Rumor has it that many (or most) of these issues will be resolved in Blackstone, thankfully. cheers, barneyb On Mon, 17 Jan 2005 14:17:41 -0800, Ian Skinner <[EMAIL PROTECTED]> wrote: > I have a fairly complicated process, where I have to build up a query from > several different sources. I have got it all working, except the last step > where I have to add the latest record to the end of the newest record. I am > trying to do this with a union in a query-of-a-query query block. I am > getting the following error. > > Query Of Queries runtime error. > All resulting columns of queries in a SELECT statement containing a UNION > operator must have corresponding types. > Columns with index number equal "3" have different types (OTHER, VARCHAR). > > Here is the code. > > SELECT > * > FROM > Data > > UNION > > SELECT > * > FROM > CurData > > > I have debugged the process so I know that I have all the same columns in > both querries. Is there an easy fix to this, or is it better if I just start > using the query functions to add data to the final query object. > > -- > Ian Skinner > Web Programmer > BloodSource > www.BloodSource.org > Sacramento, CA > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 8 invites. ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190850 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Help with Union on a query of a Query
I have a fairly complicated process, where I have to build up a query from several different sources. I have got it all working, except the last step where I have to add the latest record to the end of the newest record. I am trying to do this with a union in a query-of-a-query query block. I am getting the following error. Query Of Queries runtime error. All resulting columns of queries in a SELECT statement containing a UNION operator must have corresponding types. Columns with index number equal "3" have different types (OTHER, VARCHAR). Here is the code. SELECT * FROM Data UNION SELECT * FROM CurData I have debugged the process so I know that I have all the same columns in both querries. Is there an easy fix to this, or is it better if I just start using the query functions to add data to the final query object. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190845 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of a query inconsistancies
Barney Boisvert wrote: > That's still not the same query; it'll only work if each user only has a > single record in the timelog table. Or did you mistype and all the > 'timelogID's in the subquery should be replaced with 'userID's? Only if you mistyped it in the original :-) Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Query of a query inconsistancies
That's still not the same query; it'll only work if each user only has a single record in the timelog table. Or did you mistype and all the 'timelogID's in the subquery should be replaced with 'userID's? You make a valid point. Not sure the jump to "that's why MySQL has that feature" is really reasonable, but it's damn near irrelevant anyway. Cheers, barneyb > -Original Message- > From: Jochem van Dieten [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 08, 2004 3:59 PM > To: CF-Talk > Subject: Re: Query of a query inconsistancies > > Barney Boisvert wrote: > >> If only one of them is unique, you need to normalize your data :) > > > > I need to pull out a list of users and the number of hours > they've worked in > > the past year: > > > > SELECT user.userID, user.firstname, user.lastname, > > SUM(timelog.endTime - timelog.startTime) AS timeWorked > > FROM user > > INNER JOIN timelog ON user.userID = timelog.timelogID > > GROUP BY user.userID [ , user.firstname, user.lastname ] > > ORDER BY user.lastname, user.firstname, user.userID > > > > Why bother having the full select list (minus the aggregate > column) in the > > GROUP BY clause, when simply 'user.userID' will do the job? > > As long as your data is normalized, standard SQL doesn't require > you to have anything more in the GROUP BY clause as MySQL does as > long as you are smart about your SQL: > > SELECT u.userID, u.firstname, u.lastname, t.timeWorked > FROM user u INNER JOIN ( > SELECT timelogID, SUM(endTime - startTime) AS timeWorked > FROM timelog > GROUP BY timelogID > ) t ON u.userID = t.timelogID > ORDER BY user.lastname, user.firstname, user.userID > > > Naturally this requires you to have a DBMS that supports > subqueries and this is a query one would typically implement > using a view if it occured frequently. I believe the (then) lack > of support for subqueries and the current lack of support for > views in MySQL is a better explanation for the presence of this > 'feature' as this being an omission in the SQL standard. > > Jochem > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
Barney Boisvert wrote: >> If only one of them is unique, you need to normalize your data :) > > I need to pull out a list of users and the number of hours they've worked in > the past year: > > SELECT user.userID, user.firstname, user.lastname, > SUM(timelog.endTime - timelog.startTime) AS timeWorked > FROM user > INNER JOIN timelog ON user.userID = timelog.timelogID > GROUP BY user.userID [ , user.firstname, user.lastname ] > ORDER BY user.lastname, user.firstname, user.userID > > Why bother having the full select list (minus the aggregate column) in the > GROUP BY clause, when simply 'user.userID' will do the job? As long as your data is normalized, standard SQL doesn't require you to have anything more in the GROUP BY clause as MySQL does as long as you are smart about your SQL: SELECT u.userID, u.firstname, u.lastname, t.timeWorked FROM user u INNER JOIN ( SELECT timelogID, SUM(endTime - startTime) AS timeWorked FROM timelog GROUP BY timelogID ) t ON u.userID = t.timelogID ORDER BY user.lastname, user.firstname, user.userID Naturally this requires you to have a DBMS that supports subqueries and this is a query one would typically implement using a view if it occured frequently. I believe the (then) lack of support for subqueries and the current lack of support for views in MySQL is a better explanation for the presence of this 'feature' as this being an omission in the SQL standard. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Query of a query inconsistancies
> If only one of them is unique, you need to normalize your data :) I need to pull out a list of users and the number of hours they've worked in the past year: SELECT user.userID, user.firstname, user.lastname, SUM(timelog.endTime - timelog.startTime) AS timeWorked FROM user INNER JOIN timelog ON user.userID = timelog.timelogID GROUP BY user.userID [ , user.firstname, user.lastname ] ORDER BY user.lastname, user.firstname, user.userID Why bother having the full select list (minus the aggregate column) in the GROUP BY clause, when simply 'user.userID' will do the job? There's nothing denormalized about the schema in question: USER: userID Firstname Lastname Username Password Email ... TIMELOG: timelogID userID startTime endTime logEntry > -Original Message- > From: Jochem van Dieten [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 08, 2004 3:06 PM > To: CF-Talk > Subject: Re: Query of a query inconsistancies > > Barney Boisvert wrote: > >> For some reason in MySQL this braindead syntax is allowed, but > >> even the manual says the result may be 'unpredictable' > > > > It's not totally brain dead. > > I disagree :) > > > > I agree that if you don't know what you're > > doing, you shouldn't use it, but it is nice where you've > got a series of > > columns that you're grouping on, but one of them is unique. > > If only one of them is unique, you need to normalize your data :) > > But even in the case you are working with such a schema, go for > listing all the columns in the group by, especially if there is > no way of enforcing that all future data will also have those > characteristics. It is just to prone to future errors, not just > the usual errors introduced by changing code, but also errors > introduced by changing data. > > Jochem > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
Barney Boisvert wrote: >> For some reason in MySQL this braindead syntax is allowed, but >> even the manual says the result may be 'unpredictable' > > It's not totally brain dead. I disagree :) > I agree that if you don't know what you're > doing, you shouldn't use it, but it is nice where you've got a series of > columns that you're grouping on, but one of them is unique. If only one of them is unique, you need to normalize your data :) But even in the case you are working with such a schema, go for listing all the columns in the group by, especially if there is no way of enforcing that all future data will also have those characteristics. It is just to prone to future errors, not just the usual errors introduced by changing code, but also errors introduced by changing data. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Query of a query inconsistancies
> For some reason in MySQL this braindead syntax is allowed, but > even the manual says the result may be 'unpredictable' It's not totally brain dead. I agree that if you don't know what you're doing, you shouldn't use it, but it is nice where you've got a series of columns that you're grouping on, but one of them is unique. The only way it's "unpredictable" is if you're using it wrong. But then that's the case with anything. Cheers, barneyb [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
Ah. That link explains it all. The OrderDate fields are not unique. Thanks. - Original Message - From: "Jochem van Dieten" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, July 08, 2004 17:38 PM Subject: Re: Query of a query inconsistancies > Let me guess, you are using MySQL. > > > In standard SQL that would not include the OrderDate column > because each field you select without aggregating it needs to be > in the list of fields you group by. So in standard SQL this query > can not produce any fields other then totalcount, totalcost (both > aggregates) and UserID (group by). > > For some reason in MySQL this braindead syntax is allowed, but > even the manual says the result may be 'unpredictable', which > appears to be exactly what you are encountering: > http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html > > It is better to stay away from non standard SQL unless you > understand every detail of the consequences. > > Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
Todd Ashworth wrote: >> Todd <[EMAIL PROTECTED]> wrote: >>> >>> >>> SELECT *, COUNT(*) AS totalcount, SUM(Cost) AS totalcost >>> FROM orders, orderitems >>> WHERE orders.OrderID = orderitems.OrderID >>> AND OrderDate >= '#DateFormat(StartTime, "-mm-dd")#' >>> GROUP BY UserID >>> HAVING totalcount > 1 >>> > It does. On the first line I'm doing a SELECT *. that would include the > OrderDate column, would it not? Let me guess, you are using MySQL. In standard SQL that would not include the OrderDate column because each field you select without aggregating it needs to be in the list of fields you group by. So in standard SQL this query can not produce any fields other then totalcount, totalcost (both aggregates) and UserID (group by). For some reason in MySQL this braindead syntax is allowed, but even the manual says the result may be 'unpredictable', which appears to be exactly what you are encountering: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html It is better to stay away from non standard SQL unless you understand every detail of the consequences. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
It does. On the first line I'm doing a SELECT *. that would include the OrderDate column, would it not? - Original Message - From: "I-Lin Kuo" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, July 08, 2004 10:31 AM Subject: Re: Query of a query inconsistancies > Logically, they're different. You shouldn't get any > results by moving the orderdate condition to the > bottom query, because qryGetRepeatSales doesn't have a > date column > > --- Todd <[EMAIL PROTECTED]> wrote: > > I have these 2 queries: > > > > > > name="qryGetRepeatSales"> > > SELECT *, COUNT(*) AS totalcount, SUM(Cost) AS > > totalcost > > FROM orders, orderitems > > WHERE orders.OrderID = orderitems.OrderID > > AND OrderDate >= '#DateFormat(StartTime, > > "-mm-dd")#' > > GROUP BY UserID > > HAVING totalcount > 1 > > > > > > > > SELECT SUM(totalcost) AS totalcost, > > COUNT(totalcount) AS totalcount > > FROM qryGetRepeatSales > > > > > > The problem I'm having is when the "OrderDate" line > > is in the top query, I get the correct results. If > > I move it to the bottom query, the totalcost and > > totalcount end up being 1 or 2 less. The date > > filter really needs to be in the bottom query. What > > am I missing? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
Logically, they're different. You shouldn't get any results by moving the orderdate condition to the bottom query, because qryGetRepeatSales doesn't have a date column --- Todd <[EMAIL PROTECTED]> wrote: > I have these 2 queries: > > > name="qryGetRepeatSales"> > SELECT *, COUNT(*) AS totalcount, SUM(Cost) AS > totalcost > FROM orders, orderitems > WHERE orders.OrderID = orderitems.OrderID > AND OrderDate >= '#DateFormat(StartTime, > "-mm-dd")#' > GROUP BY UserID > HAVING totalcount > 1 > > > > SELECT SUM(totalcost) AS totalcost, > COUNT(totalcount) AS totalcount > FROM qryGetRepeatSales > > > The problem I'm having is when the "OrderDate" line > is in the top query, I get the correct results. If > I move it to the bottom query, the totalcost and > totalcount end up being 1 or 2 less. The date > filter really needs to be in the bottom query. What > am I missing? > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Query of a query inconsistancies
I have these 2 queries: SELECT *, COUNT(*) AS totalcount, SUM(Cost) AS totalcost FROM orders, orderitems WHERE orders.OrderID = orderitems.OrderID AND OrderDate >= '#DateFormat(StartTime, "-mm-dd")#' GROUP BY UserID HAVING totalcount > 1 SELECT SUM(totalcost) AS totalcost, COUNT(totalcount) AS totalcount FROM qryGetRepeatSales The problem I'm having is when the "OrderDate" line is in the top query, I get the correct results. If I move it to the bottom query, the totalcost and totalcount end up being 1 or 2 less. The date filter really needs to be in the bottom query. What am I missing? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Query of a query - sort order not correct?
If find that whatever your ordering on or grouping by or filtering on always need to be present in the SELECT clause as well when using QoQ (which aint the case when dealing with a database version of SQL -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: 02 December 2003 12:47 To: CF-Talk Subject: RE: Query of a query - sort order not correct? Try this... SELECT UPPER(IsDirectory) AS dirName, Name FROM RootFiles ORDER BY dirName DESC, Name -Original Message- From: Bushy [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 7:38 AM To: CF-Talk Subject: re: Query of a query - sort order not correct? Hi, I'm doing a query on a query but it won't sort properly. With the "upper" it gives an error saying lower(IsDirectory) is not a column that can be sorted on. Why? If I remove the "upper" to just ORDER BY IsDirectory DESC, Name the code will run but the sorting order is not correct. SELECT IsDirectory, Name FROM RootFiles ORDER BY upper(IsDirectory) DESC, Name _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Query of a query - sort order not correct?
Try this... SELECT UPPER(IsDirectory) AS dirName, Name FROM RootFiles ORDER BY dirName DESC, Name -Original Message- From: Bushy [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 7:38 AM To: CF-Talk Subject: re: Query of a query - sort order not correct? Hi, I'm doing a query on a query but it won't sort properly. With the "upper" it gives an error saying lower(IsDirectory) is not a column that can be sorted on. Why? If I remove the "upper" to just ORDER BY IsDirectory DESC, Name the code will run but the sorting order is not correct. SELECT IsDirectory, Name FROM RootFiles ORDER BY upper(IsDirectory) DESC, Name _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
re: Query of a query - sort order not correct?
Hi, I'm doing a query on a query but it won't sort properly. With the "upper" it gives an error saying lower(IsDirectory) is not a column that can be sorted on. Why? If I remove the "upper" to just ORDER BY IsDirectory DESC, Name the code will run but the sorting order is not correct. SELECT IsDirectory, Name FROM RootFiles ORDER BY upper(IsDirectory) DESC, Name [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: query of a query
Have you tried this? #CreateODBCDate(createdate(2002,01,01))# - Original Message - From: Admin To: CF-Talk Sent: Monday, December 24, 2001 10:00 AM Subject: query of a query I'm trying to retrieve a subset from a query cached in memory. I'm using this query: SELECT BESTNUMBER, SPOTSTART FROM application.allquery WHERE ((SPOTSTART >= '01-01-2002' But I receive the error Error: is not a valid date I already tried a few different formats including createodbcdate etc but no luck so far. Anyone here knows what does work, and maybe some extra tips for working with this feature? __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc 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 a query
Try: SELECT BESTNUMBER, SPOTSTART FROM application.allquery WHERE ((SPOTSTART >= #CreateODBCDate(Testdate)# Date fields don't normally need quotes in the SQL statement Best regards, Dennis Powers UXB Internet (203)879-2844 http://www.uxbinfo.com/ -Original Message- From: Admin [mailto:[EMAIL PROTECTED]] Sent: Monday, December 24, 2001 10:01 AM To: CF-Talk Subject: query of a query I'm trying to retrieve a subset from a query cached in memory. I'm using this query: SELECT BESTNUMBER, SPOTSTART FROM application.allquery WHERE ((SPOTSTART >= '01-01-2002' But I receive the error Error: is not a valid date I already tried a few different formats including createodbcdate etc but no luck so far. Anyone here knows what does work, and maybe some extra tips for working with this feature? __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona 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
query of a query
I'm trying to retrieve a subset from a query cached in memory. I'm using this query: SELECT BESTNUMBER, SPOTSTART FROM application.allquery WHERE ((SPOTSTART >= '01-01-2002' But I receive the error Error: is not a valid date I already tried a few different formats including createodbcdate etc but no luck so far. Anyone here knows what does work, and maybe some extra tips for working with this feature? __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb 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: Sorting a Query of a Query
Nope, no comma ... but I did figure it out - was missing a declaration for a
column in the QueryNew() line.
Joshua Miller
Web Development::Programming
Eagle Technologies Group, Inc.
www.eagletgi.com
[EMAIL PROTECTED]
-Original Message-
From: Dave Hannum [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 18, 2001 8:15 AM
To: CF-Talk
Subject: Re: Sorting a Query of a Query
You need a comma between #orderby# and #updn# for one thing.
Dave
- Original Message -
From: "Joshua Miller" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, October 17, 2001 9:47 AM
Subject: Sorting a Query of a Query
> When attempting to ORDER BY on a Query of a Query, I get the following
error
> on ALL fields:
>
> Query Manipulation Error Code = 0
> STOREID is not a column that can be sorted on.
>
> The query that I'm querying is a "Built Query" using QueryNew(),
> QueryAddRow() and QuerySetCell() to create the final query.
>
> Is this possible or am I doing something wrong?
>
> Code:
>
> default="#dateformat(dateadd('','-1',Now()),'mm/dd/')#">
>
>
>
>
>
> SELECT * FROM qGetOrders
> WHERE dateordered BETWEEN '#startdate#' AND '#enddate#'
> ORDER BY #orderby# #updn#
>
>
>
> Joshua Miller
> Web Development::Programming
> Eagle Technologies Group, Inc.
> www.eagletgi.com
> [EMAIL PROTECTED]
>
~~
Get the mailserver that powers this list at http://www.coolfusion.com
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: Sorting a Query of a Query
Have you confirmed that storeid is a column name in the query? You can do that by doing: #qGetOrders.columnlist# It's unrelated,but you also need a comma between the two sort fields i.e. ORDER BY #orderby# , #updn# If all else fails (I know this works for Oracle, I haven't tried it for anything else), if you know the ordinal position of the column, say storeid is the 2nd column and that your other sort column was the 5th, you can do ORDER BY 2 , 5 The code is not as readable but it may be a workaround. -Original Message- From: Joshua Miller [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 17, 2001 09:48 To: CF-Talk Subject: Sorting a Query of a Query When attempting to ORDER BY on a Query of a Query, I get the following error on ALL fields: Query Manipulation Error Code = 0 STOREID is not a column that can be sorted on. The query that I'm querying is a "Built Query" using QueryNew(), QueryAddRow() and QuerySetCell() to create the final query. Is this possible or am I doing something wrong? Code: SELECT * FROM qGetOrders WHERE dateordered BETWEEN '#startdate#' AND '#enddate#' ORDER BY #orderby# #updn# Joshua Miller Web Development::Programming Eagle Technologies Group, Inc. www.eagletgi.com [EMAIL PROTECTED] ~~ Get the mailserver that powers this list at http://www.coolfusion.com 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: Sorting a Query of a Query
You need a comma between #orderby# and #updn# for one thing.
Dave
- Original Message -
From: "Joshua Miller" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, October 17, 2001 9:47 AM
Subject: Sorting a Query of a Query
> When attempting to ORDER BY on a Query of a Query, I get the following
error
> on ALL fields:
>
> Query Manipulation Error Code = 0
> STOREID is not a column that can be sorted on.
>
> The query that I'm querying is a "Built Query" using QueryNew(),
> QueryAddRow() and QuerySetCell() to create the final query.
>
> Is this possible or am I doing something wrong?
>
> Code:
>
> default="#dateformat(dateadd('','-1',Now()),'mm/dd/')#">
>
>
>
>
>
> SELECT * FROM qGetOrders
> WHERE dateordered BETWEEN '#startdate#' AND '#enddate#'
> ORDER BY #orderby# #updn#
>
>
>
> Joshua Miller
> Web Development::Programming
> Eagle Technologies Group, Inc.
> www.eagletgi.com
> [EMAIL PROTECTED]
>
~~
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
Sorting a Query of a Query
When attempting to ORDER BY on a Query of a Query, I get the following error on ALL fields: Query Manipulation Error Code = 0 STOREID is not a column that can be sorted on. The query that I'm querying is a "Built Query" using QueryNew(), QueryAddRow() and QuerySetCell() to create the final query. Is this possible or am I doing something wrong? Code: SELECT * FROM qGetOrders WHERE dateordered BETWEEN '#startdate#' AND '#enddate#' ORDER BY #orderby# #updn# Joshua Miller Web Development::Programming Eagle Technologies Group, Inc. www.eagletgi.com [EMAIL PROTECTED] ~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.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 a Query
>From the CF5 docs... === Performing queries on query results has many benefits: If you need to access the same tables multiple times, you greatly reduce access time for tables with up to 10,000 rows because the data is already in memory. You can join and perform unions on results from different data sources. For example, you can do a union on queries from different databases to eliminate duplicates for a mailing list. You can efficiently manipulate cached query results in different ways. You can query a database once, and then use the results to generate several different summary tables. For example, if you need to summarize the total salary by department, by skill, and job, you can make one query to the database and use its results in three separate queries to generate the summaries. You can make drill-down, master-detail-like functionality where you do not go to the database for the details. For example, you can select information about departments and employees in a query and cache the results. You can then display the employee names. When users select an employee, the application displays the employee details by selecting information from the cached query without accessing the database. Creating queries of queries You can create a query using a query object from any ColdFusion tag or function that generates query results, including cfldap, cfdirectory, chttp, cfstoredproc, cfpop, cfindex, and the Query functions. You can use a limited subset of the SQL SELECT syntax, which includes: FROM WHERE GROUP BY UNION ORDER BY HAVING AS DISTINCT Boolean predicates: LIKE NOT LIKE IN NOT IN BETWEEN NOT BETWEEN AND OR Aggregate functions: Count([DISTINCT][*] expr) Sum([DISTINCT] expr) Avg([DISTINCT] expr) Max(expr) Min(expr) You cannot nest aggregate functions. Comparison operators: <= >= = < > <> You can also do the following tasks: Use the results of one or two queries in your SQL statement. Generate computed columns. Performing a query on a query To generate a query using an existing query: Specify the cfquery tag's dbtype attribute as "query". Do not specify a datasource attribute. Specify the names of one or more existing queries as the table names in a SQL SELECT statement. If the database content does not change rapidly, it is a good idea to use the cachedwithin attribute to cache the query results of between page requests. This way, ColdFusion accesses the database on the first page request, and does not query the database again until the specified time expires. Note that you must use the CreateTimeSpan function to specify the cachedwithin attribute value. HTH, Jeff Garza Lead Developer/Webmaster Spectrum Astro, Inc. 480.892.8200 [EMAIL PROTECTED] http://www.spectrumastro.com ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query of a Query
Select * from OrigQueryName Obviously need to be running CF5 for this to work. Matt -Original Message- From: Jordan Saardchit [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 03, 2001 1:24 PM To: CF-Talk Subject: Query of a Query Can someone please send me the syntax on using QoQ. I can't remember the sytnax to reference a result set already in memory. Thanks ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Query of a Query
In the second query, don't include "datasource", but do include 'dbType="query"' The name should be the same as in the first query. HTH, --John Jordan Saardchit wrote: > > Can someone please send me the syntax on using QoQ. I can't remember > the sytnax to reference a result set already in memory. > Thanks > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Query of a Query
Can someone please send me the syntax on using QoQ. I can't remember the sytnax to reference a result set already in memory. Thanks ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
