Oracle SQL: What is the meaning of this DECODE statement?
I'm working on storing some Oracle SQL queries written by someone else, and all over the place I see this same pattern repeated across a lot of pl / sql statements and blocks:
select DECODE(NVL(t1.some_column,'~'),'~',t2.some_column,t1.some_column) some_column from t1, t2 where ...
Now, doesn't that sound like this much simpler statement?
select NVL(t1.some_column,t2.some_column) from t1, t2 where...
I'm not sure why decoding and nvl are tied in the original request. This seems to be a less efficient way to do nvl only. Can someone explain this to me?
Thank you for understanding!
source to share
Wow, uh, looks pretty bad coding.
NVL(t1.some_column, t2.some_column)
... gives the same results.
It's more convenient for me COALESCE
, though:
COALESCE(t1.some_column, t2.some_column)
Thus, if you want to add a third, simply:
COALESCE(t1.some_column, t2.some_column, t3.some_column)
It looks like whoever wrote the original code wanted it to be extensible and just didn't know about COALESCE
...
source to share