user75ponic user75ponic - 1 year ago 81
SQL Question

NVL Column and NULL

I have rows as shown below

ProductId ProductName ProductDesc ProductLoc
101 Camel Pencil B-10
102 Parker Pen
103 Mirado Pen C-10

When I execute the following SQL query

FROM tablename
WHERE productloc = NVL ('', productloc)

It gives me the 1st and 3rd row, what I would like to achieve is if productloc is null in where condition of the SQL, then I should get all three rows.

How can I get the desired output.

Answer Source

select * from tablename;

Is what you need in that case because your where gives no effect. You compare column with itself.

If you want to filter and include nulls you can do (but probably replace one productloc with some value:

select * from tablename where productloc = productloc or productloc is null;


select * from tablename where nvl(productloc, 'SOME_UNIQUE_VAL') = nvl(productloc, 'SOME_UNIQUE_VAL');

and also replace one of productloc by some value.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download