Case Insensitive search

Some times performing case insensitive search through connection pool(alter session set NLS_SORT=BINARY_CI, alter session set NLS_COMP=LINGUISTIC) is not a feasible way.
Suppose in some report, user wants to have case sensitive search/filter, in this situation above solution is not feasible.

In those cases following method will be useful.

Make a filter using advanced sql option with reference column as uppercase column and also presentation variable.

In detail,

1).Create prompt assign default value as %%(to display each and every row) and also assign a variable to this.
2) Create report, in the report create a filter using advanced – Convert this filter to SQL option, now in this edit box make reference column as upper case and also variable.

In this way we can achieve case insensitive search problem.
Posted by

6 comments:

  1. AnonymousSeptember 16, 2009 at 3:47 PM

    Srinivas, thanks for your approach. It works fine.

    A small enhancement, if I may suggest, would be in step 1 - make the default value as % instead of %%. That way users would not see those %% characters in the edit box of the prompt and they need not bother about % characters in any of their searches. Thanks again

    ReplyDelete
  2. Srinivas MalyalaSeptember 16, 2009 at 4:05 PM

    Hi Anonymous,

    Thank you for your suggestion.
    If you pass just % as default converted physical query where class looks like this "table.column like upper('')" i.e it query for space value on that column.

    Where as we need all values right? if we need all values then we have to use %%.


    Thanks,
    Srinivas

    ReplyDelete
  3. AnonymousSeptember 16, 2009 at 4:16 PM

    Srinivas, I created a prompt with default to specific value of % and it is fetching me all records.

    What happens in this case is: the SQL built by OBI will look like this: Select...where column name like '%%%'. And, Oracle processes '%%%' the same way as '%%', thus returning all the records.

    You may want to try it for yourself and see the results.

    ReplyDelete
  4. Srinivas MalyalaSeptember 16, 2009 at 4:20 PM

    @Anonymous,

    May be you are using upgraded version because of that it showing all, nice to see that one.
    Cool thanks.

    --Srinivas

    ReplyDelete
  5. nicolaeancutabiMay 25, 2010 at 1:09 AM

    Hi,
    nice one...
    but if there are a lot of data to filter on, maybe it will be ok to have an function index on that upper() function...

    Regards
    Nicolae

    ReplyDelete
  6. Srinivas MalyalaMay 25, 2010 at 10:27 AM

    Thanks Nico for your valuable suggestions :)

    ReplyDelete

Please give me your feed back

Subscribe to: Post Comments (Atom)