Strange query of query behavior.
I am experiencing some very strange behavior from some very strange behavior with a recursive query of a query function. I am using the function to build a tree from a ms-sql table that has a parent-child relationship in its records. This function orders the selected records by a field that in the original query can contain nulls or integers. If I order the query of query by this field, the output loop will only iterate over the first record in the record set, even though I can dump the entire record set. If I remove the fields that can contain nulls from the query of query SQL statement, the output loops over all the records. Does this make sense? Should one expect this behavior from a query-of-query? How can the cfdump... tag loop over all the records but the cfoutput... does not? The output of this page can be seen here: http://www.sierraoutdoorrecreation.com/Geo-Order.cfm ~| 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:323066 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Strange Query of Query Behavior
Hello, I am doing a query that extracts all sales info for an employee in a particular time frame. Then I am doing 2 querys of that query as seen: cfquery name=SalesCalc1 dbtype=query SELECT SalesAmount AS Sales1Sum FROM SalesQuery Where BonusDeal = 1/cfquery cfquery name=SalesCalc2 dbtype=query SELECT SalesAmount AS Sales2Sum FROM SalesQuery Where BonusDeal = 0/cfquery This is working correctly, showing 2 different (correct) values for the amounts. Sales1Sum: 10,000 Sales2Sum: 15,000 But, when I want to use the SUM function in the SQL it shows totals for both as the same total. cfquery name=SalesCalc1 dbtype=query SELECT SUM(SalesAmount) AS Sales1Sum FROM SalesQuery Where BonusDeal = 1/cfquery cfquery name=SalesCalc2 dbtype=query SELECT SUM(SalesAmount) AS Sales2Sum FROM SalesQuery Where BonusDeal = 0/cfquery Sales1Sum: 25,000 Sales2Sum: 25,000 So it appears that the Query of Query is not taking the WHERE clause into consideration.. Any Ideas on why? Is this a Bug? How should I proceed? Thanks Everyone, -Dan __ 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: Strange Query of Query Behavior
that is strange. Have you tried the HAVING clause instead of WHERE? - Original Message - From: Daniel Quinones [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, February 14, 2002 6:24 PM Subject: Strange Query of Query Behavior Hello, I am doing a query that extracts all sales info for an employee in a particular time frame. Then I am doing 2 querys of that query as seen: cfquery name=SalesCalc1 dbtype=query SELECT SalesAmount AS Sales1Sum FROM SalesQuery Where BonusDeal = 1/cfquery cfquery name=SalesCalc2 dbtype=query SELECT SalesAmount AS Sales2Sum FROM SalesQuery Where BonusDeal = 0/cfquery This is working correctly, showing 2 different (correct) values for the amounts. Sales1Sum: 10,000 Sales2Sum: 15,000 But, when I want to use the SUM function in the SQL it shows totals for both as the same total. cfquery name=SalesCalc1 dbtype=query SELECT SUM(SalesAmount) AS Sales1Sum FROM SalesQuery Where BonusDeal = 1/cfquery cfquery name=SalesCalc2 dbtype=query SELECT SUM(SalesAmount) AS Sales2Sum FROM SalesQuery Where BonusDeal = 0/cfquery Sales1Sum: 25,000 Sales2Sum: 25,000 So it appears that the Query of Query is not taking the WHERE clause into consideration.. Any Ideas on why? Is this a Bug? How should I proceed? Thanks Everyone, -Dan __ 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: Strange Query of Query Behavior
It would appear that you have found a bug. I've reproduced your error as well. When computing a sum on a column it is missing the where clause. I'd report it to macromedia and see what they have to say... Now, to solve your problem... What database are you using? Can you run this as a stored proceedure? Jeff Garza -Original Message- From: Daniel Quinones To: CF-Talk Sent: 2/14/02 5:24 PM Subject: Strange Query of Query Behavior Hello, I am doing a query that extracts all sales info for an employee in a particular time frame. Then I am doing 2 querys of that query as seen: cfquery name=SalesCalc1 dbtype=querySELECT SalesAmount AS Sales1Sum FROM SalesQuery Where BonusDeal= 1/cfquery cfquery name=SalesCalc2 dbtype=querySELECT SalesAmount AS Sales2Sum FROM SalesQuery Where BonusDeal= 0/cfquery This is working correctly, showing 2 different (correct) values for the amounts. Sales1Sum: 10,000 Sales2Sum: 15,000 But, when I want to use the SUM function in the SQL it shows totals for both as the same total. cfquery name=SalesCalc1 dbtype=query SELECT SUM(SalesAmount) AS Sales1Sum FROM SalesQuery Where BonusDeal = 1/cfquery cfquery name=SalesCalc2 dbtype=query SELECT SUM(SalesAmount) AS Sales2Sum FROM SalesQuery Where BonusDeal = 0/cfquery Sales1Sum: 25,000 Sales2Sum: 25,000 So it appears that the Query of Query is not taking the WHERE clause into consideration.. Any Ideas on why? Is this a Bug? How should I proceed? Thanks Everyone, -Dan __ 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
