Yep....I'll second this....would bet it's against a different DB BTW...a CFLOCATION in the middle of a query just feels plain wrong....
On Fri, 2010-10-22 at 17:16 -0400, Richard Dillman wrote: > I'd say thatCF is not the problem here. LEN is a database function in this > context. Are your CF8 and CF5 both pointing at the same data source? > Different DB's handle the length function differently.. > SQL uses LEN() > MYSQL is LENGTH() > ... > > Just a thought. > > On Fri, Oct 22, 2010 at 5:02 PM, John McLeod <[email protected]>wrote: > > > > > I have a cfquery code that looks like this: > > > > <CFQUERY DATASOURCE="mydatabase" USERNAME="myuser" PASSWORD="mypassword" > > NAME="codelist" CACHEDWITHIN="#CreateTimeSpan(0,1,0,0)#"> > > SELECT * > > FROM systemcode > > WHERE (LEN(TRIM(systemcode)) = 2 OR LEN(TRIM(systemcode)) = 4) > > <CFSWITCH EXPRESSION="#CAT#"> > > <CFCASE VALUE="1">AND (MID(systemcode,1,2) BETWEEN '01' AND > > '05')</CFCASE> > > <CFCASE VALUE="2">AND (MID(systemcode,1,2) BETWEEN '06' AND > > '15')</CFCASE> > > <CFCASE VALUE="3">AND (MID(systemcode,1,2) BETWEEN '16' AND > > '24')</CFCASE> > > <CFCASE VALUE="4">AND (MID(systemcode,1,2) BETWEEN '25' AND > > '27')</CFCASE> > > <CFCASE VALUE="5">AND (MID(systemcode,1,2) BETWEEN '28' AND > > '38')</CFCASE> > > <CFCASE VALUE="6">AND (MID(systemcode,1,2) BETWEEN '39' AND > > '40')</CFCASE> > > <CFCASE VALUE="7">AND (MID(systemcode,1,2) BETWEEN '41' AND > > '43')</CFCASE> > > <CFCASE VALUE="8">AND (MID(systemcode,1,2) BETWEEN '44' AND > > '49')</CFCASE> > > <CFCASE VALUE="9">AND (MID(systemcode,1,2) BETWEEN '50' AND > > '63')</CFCASE> > > <CFCASE VALUE="10">AND (MID(systemcode,1,2) BETWEEN '64' AND > > '67')</CFCASE> > > <CFCASE VALUE="11">AND (MID(systemcode,1,2) BETWEEN '68' AND > > '71')</CFCASE> > > <CFCASE VALUE="12">AND (MID(systemcode,1,2) BETWEEN '72' AND > > '83')</CFCASE> > > <CFCASE VALUE="13">AND (MID(systemcode,1,2) BETWEEN '84' AND > > '85')</CFCASE> > > <CFCASE VALUE="14">AND (MID(systemcode,1,2) BETWEEN '86' AND > > '89')</CFCASE> > > <CFCASE VALUE="15">AND (MID(systemcode,1,2) BETWEEN '90' AND > > '97')</CFCASE> > > <CFCASE VALUE="16">AND (MID(systemcode,1,2) BETWEEN '98' AND > > '99')</CFCASE> > > <CFDEFAULTCASE><CFLOCATION URL="somewhere.htm"></CFDEFAULTCASE> > > </CFSWITCH> > > ORDER BY systemcode > > </CFQUERY> > > > > This works perfectly in CF 5. However, when running in CF 8 I am getting > > the error message: > > > > Error Executing Database Query.FUNCTION mydatabase.LEN does not exist The > > specific sequence of files included or processed is: /home/httpd/vhosts/ > > somedomain.com/httpdocs/reference/systemcode.cfm, line: 24 > > > > (that refers to the above cfquery) > > > > "mydatabase" is the name of the database and LEN is a function. It's > > checking for the length of systemcode to be 2 or 4, if they are then check > > if the value of #CAT# is 1 and that the first 2 characters is between 01 and > > 05 then select the row. I don't know why it works with CF5 but not CF8? > > It's driving me nuts. Any help is appreciated. > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3369 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
