There are four field in the page lets say
EMPLOYEE ID
DEPT
LOCATION
UNIT:
User might enter any of the field values all are optional, if he enter EMPLOYEE ID then the query should return rows related to that EMPLOYEE ID. If he enters only LOCATION then the query should return all the employees of that location. How to write the where clause condition with optional parameters.
Oracle will likely build a well-optimized query if you use NVL in your predicates:
select *
from employee
where employee_id = nvl(:employee_id, employee_id)
and dept = nvl(:dept, dept)
and location = nvl(:location, location)
and unit = nvl(:unit, unit)
The above code is mostly equivalent to LeoLozes's answer. Although his answer is more readable, in this case the cryptic version may run much faster. One important difference is that the above code will not work if the column is NULL. If you have nullable columns you'll need to use something like LeoLoze's answer, since null = null is not true.
Oracle is used to the NVL trick, and can automatically convert this static query into a dynamic query using a FILTER operation. The execution plan will have both a FULL TABLE SCAN and and INDEX RANGE SCAN, and will pick the appropriate one at run-time, depending on the value of the bind variable. See my answer here for some sample code demonstrating how this works.
Well, there's always the (very poorly optimized) option of doing it this way:
SELECT *
FROM EMPLOYEE
WHERE (EMPLOYEE_ID = :p_EMPLOYEE_ID OR :p_EMPLOYEE_ID IS Null)
AND (DEPT = :p_DEPT OR :p_DEPT IS Null)
AND (LOCATION = :p_LOCATION OR :p_LOCATION IS Null)
AND (UNIT = :p_UNIT OR :p_UNIT IS Null)
I only use it in tables with a small amount of rows. It's however recommended to have at least one mandatory parameter that will use indexed fields (since here, you'll have a TABLE ACCESS FULL).
I would recommend against this thinking for real world applications .Submitting dynamic style query to database has proven issues in terms of security ,optimization and functional correctness . Since there will be some application code between user interface and the database, its better build the query as per need there and then submit it for execution .
select *
from employee
where nvl(employee_id, -1) = coalesce(:employee_id, employee_id, -1)
and nvl(dept, -1) = coalesce(:dept, dept, -1)
and nvl(location, -1) = coalesce(:location, location, -1)
and nvl(unit, -1) = coalesce(:unit, unit, -1)
The reasoning behind this is that using nvl the way Jon suggested has one drawback: if the field being tested is null itself, it will return false in that test because (I believe) for testing null you need to test it with is null.
nvl could be doing a null = null there instead of a null is null, returning wrong data and you won't even notice because it doesn't warn you.
Using coalesce, you will get -1 = -1 (or whatever other value you would consider as impossible for the column) when both the parameter and the field are null
Only safe way to write the code for this many is individual views based on what they are selecting, For example if your using a Java GUI you are able to select the information depending on what they want to search by.
