>From what I know about SQL Server's behaviour, it will allow for that syntax until it reaches a character then it'll stop.
In our Accpac database, we have invoices stored. Our older invoices have INV000001 or whatever for invoice numbers. Now they're imported from the database I built and the invoice number is an autonumber integer. Anyway... I routinely forget it's a char field, and forget the quotes. I always get "Error converting 'INV000001' to integer data type" (or something like that)... So I suspect it converts each record to int first before doing the where condition. I'd bet Oracle does something similar. Probably something in the standard that allows for that kind of a comparison. It'd be nice if it did it the other way and converted the where criteria to char, but that could yield much different results, especially if you use greater than or less than signs. So to accommodate for other operators, it likely does the casting the other way. -----Original Message----- From: Ruggiero, Kevin D [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 3:10 PM To: CF-Talk Subject: RE: Learned something today (Oracle) Is there orderno field a VARCHAR(2) or an number field? If it's a VARCHAR, then it is supposed to be surrounded in quotes. If it's numeric, it should not be in quotes (I don't even think it WOULD work). > -----Original Message----- > From: Tony Schreiber [mailto:[EMAIL PROTECTED] > Sent: Wednesday, April 02, 2003 5:58 PM > To: CF-Talk > Subject: Learned something today (Oracle) > > > I couldn't understand why some queries on tables selecting using the > primary key (orderno) was taking so long... > > In this instance the field in the database is a varchar > field, but it's > holding a numeric value. (Don't ask me why, that's another discussion) > > If I did the query like this: > > SELECT FROM orderheader WHERE orderno = 60134445 > > It took like 40 secs! > > If I did the query like this: > > SELECT FROM orderheader WHERE orderno = '60134445' > > Bam! tiny milliseconds... Like it should be. > > I never realized that the (lack of) quotes was the problem. > What causes > this slowdown exactly? > > > Tony Schreiber, Senior Partner Man and > Machine, Limited > mailto:[EMAIL PROTECTED] > http://www.technocraft.com > > http://www.is300.net The Enthusiast's Home of the Lexus IS300 > since 1999 > http://www.simplemessageboard.com Free Forum Software for > Cold Fusion > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
