Of course I meant we avoid the use of Query of Queries, not cfquery. :-)
On Thu, 21 Oct 2004 13:32:08 -0400, Brian Kotek <[EMAIL PROTECTED]> wrote: > There are all sorts of bugs like this in query of queries. This is > because CFMX is assuming the data type of each column based on the > first value in the query. So if you have a field that has bunch of > strings in it, but one row has a numeric value in that column, even if > YOU want it to be treated like a string, CFMX has other plans. You'll > get this error. It's gotten so bad for us that we've written CFCs > that take the place of query of queries in many cases by looping over > the queries and manually doing joins, etc. I'm hopeful they'll fix > this in Blackstone but who knows. For now, we avoid all use of > cfquery because even if it works now, if your data changes, one day it > will all suddenly blow up. > > > > > On Thu, 21 Oct 2004 13:25:13 -0400, Jonathan Nickle > <[EMAIL PROTECTED]> wrote: > > Data set example: > > > > Table name: Orders > > ******************************************* > > BatchNumber, CheckNumber, Name > > 1111a, 1234, Joe Johnson > > 1111a, 1235, Mary Madison > > 12-32-1, 3434, Jon Johnson > > 12-32-1, 3453, Victoria V > > ******************************************** > > > > <cfquery name="getOrders" dataousre="#DSN#"> > > Select * from Orders > > </cfquery> > > > > <cfloop query="GetOrders"> > > <cfset thisBatch = GetOrders.BatchNumber> > > > > <cfquery name="getBatchOrders" dbtype="query"> > > select * > > from getOrders > > where BatchNumber = '#thisBatch#' > > </cfqery> > > > > <cfloop query...> > > .... > > </cfloop> > > </cfloop> > > > > The loop works until I reach the batch with two hyphens in it. It > > handles numeric values, alphanumeric valuse, lenghts that are short and > > long, and one hypen in the string... but once there are two hyphens in the > > batch number it fails. > > > > It is NOT a possiblity to restrict two hyphens on the input as this is > > a potentially valid batch number. The SQL statement works as straight > > SQL against the DB, but fails when queried against a query. > > > > IS THIS A CF BUG?!!! > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| The annual ColdFusion User Conference is being held Sat 6/26 - Sun 6/27/04 8am-5pm in the Washington DC Area. http://www.houseoffusion.com/banners/view.cfm?bannerid=44 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:182234 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
