1) You have an existing SQL Report.
2) You want to provide functionality to search through the result set.
1) add a text_item for searching PX_search where X is page number
2) add a button which resubmits the page. Go
3) You modify your query by adding the following to the end of the where clause.
and (PX_SEARCH is null or instr(column_name1,PX_SEARCH) > 0 )
4) So far so good but it only allows searches on 1 column, so you add a few other columns
and (PX_SEARCH is null or instr(column_name1,PX_SEARCH) > 0 or instr(column_name2,PX_SEARCH) > 0 or instr(column_name3,PX_SEARCH) > 0 )
Now when you enter some text if it is in any of the columns that row will be displayed.
This can be good or bad depending on your requirement. lets say my users want to specify the column they are searching on.
i) add a LOV to your page which queries the all_users data dictionary view
select column_name d, column_name r from all_users where table_name = 'MY_TABLE_NAME' and column_name not in ('COLUMN_NAME1,COLUMN_NAME2) order by column_name
The idea here is to use the oracle data dictionary to get all the columns for your table(s) and ignore the columns you do not want.
ii) add an item to your page e.g. PX_select_column which gets populated from the list of values.
Now for each column you are going to allow a search on, the sql needs to be modified.
Lets pretend that my firstcolumn is called member_id, my second column is called firstname and my third column is lastname
Before we had this SQL
(select * from table where 1=1)
and (PX_SEARCH is null or instr(memberid,PX_SEARCH) > 0 or instr(firstname,PX_SEARCH) > 0 or instr(lastname,PX_SEARCH) > 0 )
Remember we have added PX_SELECT_COLUMN which should contain what we have selected from the LOV.
iii) So now we modify the query to be as follows:
and (PX_SEARCH is null or decode(:PX_SELECT_COLUMN,'MEMBERID',instr(memberid,PX_SEARCH),0) > 0 or
decode(:PX_SELECT_COLUMN,'FIRSTNAME',instr(firstname,PX_SEARCH),0) > 0 or
decode(:PX_SELECT_COLUMN,'LASTNAME',instr(lastname,PX_SEARCH),0) > 0 )
The user can now choose which column to filter their results on.