As of now I am using IF ELSE to handle this condition
IF INPUT_PARAM IS NOT NULL
SELECT ... FROM SOMETABLE WHERE COLUMN = INPUT_PARAM
ELSE
SELECT ... FROM SOMETABLE
Is there any better way to do this in a single query without IF ELSE loops. As the query gets complex there will be more input parameters like this and the amount of IF ELSE required would be too much.
One method would be to use a variant of
WHERE column = nvl(var, column)
There are two pitfalls here however:
if the column is nullable, this clause will filter null values whereas in your question you would not filter the null values in the second case. You could modify this clause to take nulls into account but it turns ugly:
WHERE nvl(column, impossible_value) = nvl(var, impossible_value)
Of course if somehow the impossible_value is ever inserted you will run into some other kind of (fun) problems.
nvl, you will get full scan even if perfectly valid indexes are present.This is why when there are lots of parameters (several search fields in a big form for example), I like to use dynamic SQL:
DECLARE
l_query VARCHAR2(32767) := 'SELECT ... JOIN ... WHERE 1 = 1';
BEGIN
IF param1 IS NOT NULL THEN
l_query := l_query || ' AND column1 = :p1';
ELSE
l_query := l_query || ' AND :p1 IS NULL';
END IF;
/* repeat for each parameter */
...
/* open the cursor dynamically */
OPEN your_ref_cursor FOR l_query USING param1 /*,param2...*/;
END;
You can also use EXECUTE IMMEDIATE l_query INTO l_result USING param1;
This should work
SELECT ... FROM SOMETABLE WHERE COLUMN = NVL( INPUT_PARAM, COLUMN )
