I wrote a search tool using Oracle Apex 5 which has 2 specific filter/search fields. I am attempting to search one or both, but I am not getting the desired result. Let's say I have Name Surname columns in my table and use Name Surname search field. The data will therefore look like this:
Name | Surname
--------------------
Phil | Anselmo
Max | Cavalera
Maynard | Keenan
Kurt | Cobain
David | Gilmour
My Search fields in Apex will be:
:P1_NAME
:P1_SURNAME
I want match either of the following criteria:
regexp_like(NAME, :P1_NAME, 'i') and regexp_like(SURNAME, :P1_SURNAME, 'i')
or
:P1_NAME is null and regexp_like(SURNAME, :P1_SURNAME, 'i')
or
:P1_SURNAME is null and regexp_like(NAME, :P1_NAME, 'i')
But I am unsure of how to incorporate it into a single select? Here is my latest select. I tried quite a few others though.
select name, surname, from leads
where :P1_SURNAME is null and regexp_like(NAME, :P1_NAME, 'i')
or :P1_NAME is null and regexp_like(SURNAME, :P1_SURNAME, 'i')
or regexp_like(NAME, :P1_NAME, 'i') and regexp_like(SURNAME, :P1_SURNAME, 'i')
Your select seems fine if you add braces for grouping conditions: