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
I believe your functions are not equivalent. Here is a counter example:
ReplyDeleteCASE
WHEN NVL(1, 1) = 1 THEN
NULL
ELSE
1
END AS alt_value
--> null
nvl2(1,1,null)
--> 1
I like to verify simplifications like this with a small plsql block consisting of a nested loops for each of the variables and a check for equality of the expressions. I usually include a dbms_output of the variables and the results
...and to complete the confusion there is another function, which does quite the same but better: try coalesce (http://docs.oracle.com/cd/E11882_01/server.112/e17118/functions030.htm#i1001341)
ReplyDeleteCASE WHEN custom_value<>normal_value THEN normal_value
ReplyDeleteEND
Jan
has it.
ReplyDelete@Peter - coalesce is like a lazy cascading nvl; it does no comparison between its arguments. I often use coalesce instead of nvl because of its laziness.
@llayland Correct, if the values were equal, then these functions would not return the same result (added example in post). However, there was an unmentioned assertion that those values would never be equal.
ReplyDeleteAdditionally, but final comment about "referencing each field once only" - this is a reference to performance issues about the true expression behind these generic examples.
@Peter Coalesce certain has advantages, it certainly has performance benefits.
However, in this case it is not appropriate (included SQL in post above)
@anonymous (Jan) Elegant, but I think it's more an off the backboard shot, whose mechanics isn't immediately clear.
On a tangent, I have seen mutterings of potential issues with using <> over != Found this after a quick search, but I've seen further investigations in the past.
@llayland "has it" ?
What do you mean by a "lazy" function? No comparison between its arguments?
**
In my examples the 'mixer' won't occur thanks to a check constraint, nor will the 'lost sock' appear... get it?
;-)
"anonymous has it"
ReplyDeleteLazy means it only evaluates its arguments if it needs to.
coalesce(null, expensive_function()) returns quickly because it does not call expensive_function
Never heard the word 'lazy' used to describe that ;-)
ReplyDelete"Short circuit evaluation" perhaps - or is that too textbook?
depends on the textbook :)
ReplyDeletehttp://en.wikipedia.org/wiki/Lazy_evaluation
If I really wanted to be technical I would say "call by name" since lazy implies sharing to avoid duplicate calculations which I do not think coalesce does.
I've always understood Lazy Evaluation = Short-circuit Evaluation.
ReplyDeleteAlso, I'm very dubious about there being any actual performance difference between <> and !=. Reading the linked posts I'd suspect a cached query plan, that gets reparsed when the operator is changed; or, perhaps a stored outline.
textbooks indeed!
ReplyDeleteI agree, Jeff - although I'm sure I saw a more valid reason in the past!