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_valueI 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
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.
I don't know if it's lack of familiarity with the NULL-related functions, or some people don't immediately consider them.
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.
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;