Partial search on object with multiple values

[Former Member]

Hi all,

Please help me on the requirement...

I need to create a query filter in which user wants to search with multiple objects and with partial data for ex:- i have data like

1. This report belongs to Australia.

2. Happy christmas

3. Have a lovely day

4.Good evening chennai

5. American express

Like this  i may contain vast data,here user needs to search in the prompt as "Hap,repo. Then we need to get output as

1.Thia report belongs to Australia

2.Happy Christmas

Is this search is possible in BO or any other process to acheive the same.

Thanks,

Madhavi.G

[Former Member]

HI,

hope this would help.

The following steps should be done in SP. pass the prompt value to SP and do following steps

1. break the search srting into different different srtings (using , as the seperator)

2. store the the string parts in varibles

3.write the in where condition saying

select * from tables

table. column1 like '%var1%' or

table.column1 like '%var2%'

union

select * from tables

table. column 2 like '%var1%' or

table.column 2 like '%var2%'

... etc

- SRI

[Former Member]

Partial search is not a problem but multiple string search would not work in the prompt selection.

User will have to search one partial string first and selection the values and then do the second partial string search and select..

[Former Member]

Hi Mishra,

THanks for the reply but teh customer cannot compromise on his requirement. He needs to be done this for sure, we are trying the same with stored procedures and derived tables and at report level.

Report level will slow up teh p[erformance.

In SP we acheive the process for 1 column but is it possible to have th esame for all the objects. PFB, the sp we created help me on this.

Create or replace procedure P_Search(val varchar2 default ' ') is
v1 varchar2(50) ;

begin
v1:=val;
v1:=f_replace(val);
execute immediate 'Truncate table search_list';

execute immediate  '
INSERT INTO SEARCH_LIST SELECT DISTINCT AREA=&prompt FROM D_NC WHERE REGEXP_LIKE(lower(AREA),
lower(' || chr(39) || V1|| chr(39) || '))';
commit ;
end;

Thanks,

Madhavi.G

[Former Member]

Hi Madhu,

Can you explain what exact business benefits the users are planning to gain or what business problem this process will solve..

The store proc approach will work for single column just fine but unless you handle for all column it wont work.. try with a Or criteria for all the columns while creating the SP...

The process will impact the report perform as string search is not a good performing function..