Re: newbie question: case problem

On 16 Jan 2004, eliepins_at_nrcan.gc.ca wrote:
> I am working on a search function for a database using
> ColdFusion - the database is Oracle. The first attempt at the
> code worked until a user put in a given name without the
> capital, then no results were generated. In order to address
> this problem, I added lower to the code. Now I achieve no
> results at all. Any suggestions?
>
>
> <cfloop index="i" list=#form.keyword# delimiters=" ">
> select * from archive
> where lower(projectnum) like '%lower(#i#)%'
> or lower(title) like '%lower(#i#)%'
> or lower(keywords) like '%lower(#i#)%'
> or lower(investigators) like '%lower(#i#)%'
> or lower(location) like '%lower(#i#)%'
> or lower(establishment) like '%lower(#i#)%'
> or lower(maps) like '%lower(#i#)%'
> or lower(complocation) like '%lower(#i#)%'
> or lower(reference) like '%lower(#i#)%'
> <cfif isnumeric(i)>
> UNION
> select * from archive
> where boxnum = #i#
> </cfif>
> UNION
> </cfloop>
> select * from archive where 1=2
>
> </cfquery>

lower doesn't go inside the quotes.

I'd suggest first executing your SQL inside SQLPlus before you try to make some program execute their version of it.

-- 
Galen Boyer