This content has been marked as final. Show 9 replies
Looks like your cfc code is case sensitive.
yes, as Dan said, you seem to be performing case-sensitive checking of
names in your db. depending on your db, use available string
manipulation functions to convert the case. i.e. in MySQL it would be
WHERE LOWER(first_name) LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="#lcase(keyword)#%"> OR LOWER(last_name) LIKE <cfqueryparam
your db's function to convert the case of column value may be different...
Alas, it is not case sensitive. Here is my query:
select v.firstname || ' ' || v.lastname name
from consultants_v v, employees_v ae
where v.employee_id = ae.employee_id
AND (upper(v.firstname) like upper('#search#%')
OR upper(v.lastname) like upper('#search#%'))
group by v.lastname, v.firstname
ORDER BY name
that is not right. you are trying to use a db function on a cf variable.
it should instead be '#ucase(search)#%'
your query is probably better re-written using INNER JOIN:
SELECT v.fistname || ' ' || v.lastname name
FROM consultants_v v INNER JOIN employees_v ae ON v.employee_id =
WHERE (UPPER(v.firstname) LIKE '#UCASE(search)#%') OR (UPPER(v.lastname)
GROUP BY v.lastname, v.firstname
ORDER BY name
btw, i am not sure you need that GROUP BY clause there at all...
and, please, DO use <cfqueryparam> tag in your queries!
Even though my data is returned correctly from the query I took your advice and switched to the CF ucase() function. I am still having the same problem.
I add in cfqueryparam before I make pages live, but I do use it.
Also, I'm pretty sure that IS an inner join. My database does not allow for "INNER JOIN" syntax.
"inner join" means the same as "join" It's also equivalent to the syntax of the query you posted.
To troubleshoot your problem, you have to run your query in circumstances where you can see debugging info. I'm guessing autosuggest is not one of those circumstances.
Right, Oracle 8i allows inner joins, but not "inner join".
Anyway, you're probably right about autosuggest. I'll probably have to have it return both "Bart Simpson" and "Simpson, Bart" which will be a small pain later. CFDEBUG is probably the best I can get. I still think this is a bug :)
It works if I return "Simpson Bart" but not "Simpson, Bart", even if I return valueList(query.value, ';')
Oh well, good enough.