Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

What is the best way in nullable text comparison

nvl2lifeMar 19 2013 — edited Mar 20 2013
I 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.
This post has been answered by Frank Kulash on Mar 19 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 17 2013
Added on Mar 19 2013
6 comments
307 views