Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    28

    Unanswered: Oracle ASP Case problem :(

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle ASP Case problem :(

    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.

  3. #3
    Join Date
    Aug 2002
    Posts
    28
    Thankyou!!!!!! This fixed the problem.

    Cheers for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •