decode versus nvl

[BJCooperIT]

Is there a best practice rule-of-thumb regarding using DECODE when a simple NVL would do? I am trying to convince my client that something this:

UPDATE address
SET    county_code  = decode(rec.county_code,
                             NULL,county_code,
                             rec.county_code),
       state_code   = decode(rec.state_code,
                             NULL,state_code,
                             rec.state_code),
...

should be changed to:

UPDATE address
SET    county_code  = nvl(rec.county_code,county_code),
       state_code   = nvl(rec.state_code,state_code),
...

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

Barb,

<side-bar>
I can't tell what "rec" is.
</side-bar>

Just from the simplicity/ease of reading, I would prefer your NVL over the DECODE. What arguments does your client offer in favour of the DECODE?

My "Rule of Thumb" is, If two options offer no significant business advantage over one another, then a discussion/argument about the choice lasting longer than 1 minute has already burnt up more salary time than can be saved by either option over a 1000-year period.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[BJCooperIT]

rec." is the row name from the cursor that surrounds it. This part of a 1500 line procedure from a huge package. I am rewriting a significant portion of it (making it even larger).

I searched Steve Feuerstein's PL/SQL Best Practices, but did not find a reference there. I was hoping to be able to tell the client that, in addition to code that is easier to read and maintain, it would save them processing time or memory or it would adhere to best practice standards.

What does the client say? Have not asked yet - wanted all my ducks in a row before suggesting it.

 

[SantaMufasa]

Barb,

I have not benchmarked NVL against an equivalent DECODE, but I cannot imagine that either option would be significantly costlier than the other.

I would present your biases (as you have stated them), then if the client protests, then invoke the 1-minute rule and defer to the client since s/he is paying the bills.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]