Beyond NVL

If there's one thing I've ever learned while developing in Oracle, there is always more than one way to solve a problem.

The question is - have you done it in the simplest, most intuitive manner?

That's the bar to set, because it may be you the revisits some code 2, 6, 12 months later...

Recently, I came across some code like this:

CASE
    WHEN NVL(custom_value, normal_value) = normal_value THEN
      NULL
    ELSE
      normal_value
    END AS alt_value

I had a fair idea what it was trying to resolve, but I wasn't sure and I still had to run some scenarios to be sure.
Basically, this was the 2nd of two fields that showed a normal value and a custom value. The first field was simply
NVL(custom_value, normal_value)

The second field should be:
If there is a custom value, show the normal value, otherwise show nothing.

This case statement does this, but more like:
If there isn't a custom value, compare the normal value with itself - if it is the same, show nothing, otherwise show the normal value.

Que?

I don't know if it's lack of familiarity with the NULL-related functions, or some people don't immediately consider them.
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions002.htm#CJAFHIFF

While mentally trying to convert it to something simpler, I simplified to
NULLIF(normal_value, NVL(custom_value, normal_value))

Which translates to:
If normal value is the same as itself if the custom value is not present, then use the normal value, otherwise nothing.

Then I recognised it could be simplified further, just like resolving a fraction ;-)
NVL2(custom_value, normal_value, NULL)

If custom value is not null, use normal value, otherwise show nothing.

Which reads just like my requirement - using one function, referencing each field once only.

Super.

Scott.

Added 11 April 2012 - in response to comments

with data as 
 (select 'Mouse' widget, 10 normal_value, null custom_value from dual union all
  select 'Laptop', 1000, 800 from dual union all
  select 'Mixer', 30, 30 from dual union all
  select 'Lost sock', null, 1 from dual)
select 
   widget
  ,normal_value
  ,custom_value
  ,nvl(custom_value, normal_value) first_field
  ,case when nvl(custom_value, normal_value) = normal_value then
    null
   else
    normal_value
  end as alt_value_orig
 ,nvl2(custom_value, normal_value, null) alt_value_scott
 ,case when custom_value <> normal_value then normal_value end alt_value_anon
 ,coalesce(custom_value, normal_value) alt_value_hayland
from data; 

Cheers