I believe qoq has a very limited set of functions available. To get around that, I set the original query to also retrieve the name in lower case and in my qoq, search on the lower case set field. e.g
<cfquery name="qry1" datasource="bar"> Select firstname, lower(firstname) as lower_first from mytable </cfquery> <cfquery name="qry2" dbtype="query"> Select firstname from qry1 where lower_first like '%mat' </cfquery> HTH, Tim P. ----- Original Message ----- From: "DDE" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, October 31, 2002 7:18 AM Subject: CF MX Query of Query Lower() Function > Looks like the usage of Lower() function in a query of query does not work > as in standard SQL. > Simple example: if you have a table containing names ( first_name > middle_name and last_name) the following query of query : > > <cfquery name="myquery" dbtype="query"> > select * from myfirstquery where Lower(middle_name) like '%mat' > <cfquery> > > gives the following error message: > > "The system has attempted to use an undefined value, which usually indicates > a programming error, either in your code or some system code. > Null Pointers are another name for undefined values. " > In case on of the rows has no value for middle_name.. > > This SQL statement perfectly works in Oracle or other DB. > > Dominique > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
