hello..

I am having a problem with using cases....

I have a table called products which has some simple attributes (productID, productname, uproductname, Cost, .......)
The productID is the primary key and the uproductname is a copy of the product name converted to uppercase by a trigger.

However. my problem is that I want to build a search page on my site, where the user can type in a partial product name and the page will display the results on the same page.

Moreover, when you search for product it will only display the exact case. Therefore I decided to create an upper case attribute for the product in the table and do the query based on the upper case attribute. Once the record has been found the site would display the productname not the uproductname

I tried to use SELECT * FROM products
where UPPER(uproductname) like '%MMColparam%'

MMColParam bing the textbox on the form

I thought this would work, but obviously not.

Any Ideas????????

Thanks for your help in advance. I look forward to your reply

Paul

You have the UPPER function on the wrong side. Your query should be:

SELECT * FROM products
where uproductname like UPPER('%MMColparam%')

Alternatively, you could do without the uproductname column and trigger, and just query:

SELECT * FROM products
where UPPER(productname) like UPPER('%MMColparam%')

If you want to make use of an index, you could then create a Function Based Index on UPPER(productname). But note that since your search string begins with '%', Oracle would have to do a full scan of the index in either case.

Thankyou!!!!!! This fixed the problem.

Cheers for your help