Eval compare of two values to FALSE only when both are not null and don't Match

Question

Where clause: I should test column against value in Proc, but if at least one of them is null it should be TRUE as a result. Only when BOTH NOT NULL (it's Integer values for an ID column in a table) should the result of the compare be FALSE.

Now I have this: ...

and nvl(nvl(b.account_id, account_id_), 0) = nvl(nvl(account_id_, b.account_id), 0)

b.account__id - is Column in table (Integer), account_id_ - Param in Proc of the same type.

It works, but IMHO it looks Strange to understand what is the purpose of the evaluation. So I tried to make it more READABLE, but without really good results.

I tried COALESCE - but it needs checking for null if both are nulls. LNNVL is a good one, but it gives FALSE if THEY BOTH Equal, but I need True for this case.

Any good Thoughts?

Answer 1

If I understood it right, this should do it:
(b.account_id IS NULL OR account_id_ IS NULL OR b.account_id = account_id_)

Answer 2

This:

and nvl(nvl(b.account_id, account_id_), 0) = nvl(nvl(account_id_, b.account_id), 0)

...is the same as:

AND COALESCE(b.account_id, account_id_, 0) = COALESCE(account_id_, b.account_id, 0)

Both are overly complicated. If your parameter is null, there's no filter criteria on b.account_id because the value will match itself, or the null values will fall through to become 0 and therefore are equal.

You have your choice of the following:

AND b.account_id = COALESCE(account_id_, b.account_id)
AND (account_id_ IS NULL OR b.account_id = account_id_)

Both are portable syntax, but I lean towards using the COALESCE for sake of avoiding ORs where possible because of maintenance and sargability concerns.