When you have a classic report in Oracle Application Express (APEX) and want to make it searchable you typically add a Text Item in the region, enable Submit on Enter and add a WHERE clause to your SQL statement.
Here’s an example:
Your SQL statement probably looks like this:
select CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_STREET_ADDRESS1, CUST_CITY, CUST_STATE, CUST_POSTAL_CODE, CUST_EMAIL, CREDIT_LIMIT from DEMO_CUSTOMERS where CUSTOMER_ID = :P4_SEARCH
When you want to search for multiple customers separated by a comma, how do you do that?
So in my search field I add for example: 1,2,3 and expect to see 3 customers.
There’re a couple of options you have, I’ll list three below:
where INSTR(','||:P4_SEARCH||',', ',' || CUSTOMER_ID || ',') > 0
where REGEXP_LIKE(CUSTOMER_ID, '^('|| REPLACE(:P4_SEARCH,',','|') ||')$')
where customer_id in to_number(( select regexp_substr(:P4_SEARCH,'[^,]+', 1, level) from dual connect by regexp_substr(:P4_SEARCH, '[^,]+', 1, level) is not null ))
Which one to choose? It depends what you need… if you need readability, maybe you find INSTR easier to understand. If you need performance, maybe the last option is the better choice… so as always it depends. If you want to measure the performance you can look at the Explain Plan (just copy the SQL in SQL Workshop and hit the Explain tab).
The Explain Plan for the first SQL looks like this:
The Explain Plan for the last SQL looks like this:
The above technique is also useful when you use want checkboxes above your report, so people can make a selection. For example we select the customers we want to see:
The where clause would be identical, but instead of a comma (,) you would use a colon (:), so the first statement would be:
where INSTR(':'||:P4_SEARCH||':', ':' || CUSTOMER_ID || ':') > 0
Happy searching your Classic Report :)