What is the best way in nullable text comparison
nvl2lifeMar 19 2013 — edited Mar 20 2013I have a procedure that queries a table with supplied in parameters, and i want if the parameter is null query that column which has null values, if not query only the supplied value.
So current code as follows :
SELECT *
FROM employees emp
WHERE ( emp.first_name = in_firstname OR ( in_firstname IS NULL AND emp.first_name IS NULL ))
But there are 8 seperate parameters like this, that can be queried individually and it seems not right this way, so i want to optimize this code like this:
SELECT * FROM employees emp where nvl(emp.first_name,0) = nvl(in_firstname,0)
So what do you think ? there is no guarantee that columns i query this way, wont have rows with value 0 or negative, so i need a better approach i think but cant figure out.