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

SIGNs of Friday

I just thought I'd share this thought that came to mind when looking for a solution to a simple problem.

I learnt DECODE before I encountered CASE, so often my brain thinks that way first.

Therefore, I came up with an expression that uses SIGN, which I'm sure you don't see often.

SELECT SUBSTR(product_description,1,40)
    ||DECODE(SIGN(LENGTH(product_description)-40),1,'...') product_description
   -- CASE WHEN LENGTH(product_description) > 40 THEN '...' END product_description
FROM oe.product_information;

The case equivalent is commented.

This place ellipses at the end of truncated strings longer than 40 characters.

Short-circuit Evaluations - moving away from NVL

In the experience I've had, I've found NVL such a ubiquitous expression. I started my Oracle career on 8.1.7, and it was the only expression available to filter through null expressions.

Then along came 9i. You have to look hard in the new features guide, but coalesce arrived as a "generalization of the NVL function."

To this day, I still encounter developers who have either never heard of it, or never utilise it. The more I think about the benefits of coalesce, the more I'm thinking perhaps we should stop using NVL altogether and get into the habit of using coalesce instead - regardless how how annoying it is to type.

First reason - it's more dynamic. We can determine the first non-null expression in a list. People use CASE expressions in lieu of coalesce all the time.
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Second reason - and most important - Oracle uses short-circuit evaluation, as per the ANSI standard.

Sometimes when we are comparing two values, one value could be more expensive to evaluate - perhaps it queries a table. In the scenario below I've simplified it to having a function that sleeps for the amount of seconds supplied.

10g>  create or replace function sw_delay (pn_seconds number) return number is
  2  begin
  3     dbms_lock.sleep(pn_seconds);
  4     return pn_seconds;
  5  end;
  6  /

Function created.

10g>  SET TIMING ON
10g>  -- this will always delay 1 second
10g>  select sw_delay(1) from dual;

SW_DELAY(1)
-----------
          1

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- While my function should never evaluate, the query still takes 1s
10g>  select nvl(2, sw_delay(1)) from dual;

NVL(2,SW_DELAY(1))
------------------
                 2

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- The same applies for nvl2
10g>  select nvl2(1, 2, sw_delay(1)) from dual;

NVL2(1,2,SW_DELAY(1))
---------------------
                    2

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- The ANSI standard does not evaluate unnecessary expressions.
10g>  select coalesce(2, sw_delay(1)) from dual;

COALESCE(2,SW_DELAY(1))
-----------------------
                      2

1 row selected.

Elapsed: 00:00:00.00
10g>  
10g>  -- Decode is acceptable
10g>  select decode(1,1,2,sw_delay(1)) from dual;

DECODE(1,1,2,SW_DELAY(1))
-------------------------
                        2

1 row selected.

Elapsed: 00:00:00.01
10g>  
10g>  -- As is Case 
10g>  select case when 1 = 1 then 2 else sw_delay(1) end from dual;

CASEWHEN1=1THEN2ELSESW_DELAY(1)END
----------------------------------
                                 2

1 row selected.

Elapsed: 00:00:00.00
10g>  select case 1 when 1 then 2 else sw_delay(1) end from dual;

CASE1WHEN1THEN2ELSESW_DELAY(1)END
---------------------------------
                                2

1 row selected.

Elapsed: 00:00:00.00

As commented in-line, there are occasions where there is no need to evaluate a second expression. Most programmers are taught early on to evaluate the cheapest expressions first, to save CPU time. The NVL and NVL2 expressions is not setup for this, however COALESCE, DECODE and both CASE expressions provide short circuit evaluations - once on expression returns true, there is no need to continue evaluating the superfluous entries, control returns.

The same goes for PL/SQL. Connor McDonald provides an interesting description of this here. Sometimes for the purposes of performance, it pays to be verbose.

Another version of this can be demonstrated below:

10g>  create or replace function sw_plsql (pn_seconds number) return varchar2 is
  2  begin
  3    if nvl(pn_seconds, sw_delay(1)) = 1 then
  4      return 'do this';
  5    else
  6      return 'do that';
  7    end if;
  8  end;
  9  /

Function created.

10g>  select sw_plsql(null) from dual;

SW_PLSQL(NULL)
-------------------------
do this

1 row selected.

Elapsed: 00:00:01.00
10g>  select sw_plsql(1) from dual;

SW_PLSQL(1)
-------------------------
do this

1 row selected.

Elapsed: 00:00:01.00

Using NVL reads neatly, but regardless of the value passed, the expensive expression is always evaluated.

If it was to be rewritten with an OR operator, the expensive expression will be ignored.

10g>  create or replace function sw_plsql (pn_seconds number) return varchar2 is
  2  begin
  3    if pn_seconds is null
  4    or pn_seconds = sw_delay(1) then
  5      return 'do this';
  6    else
  7      return 'do that';
  8    end if;
  9  end;
 10  /

Function created.

10g>  select sw_plsql(null) from dual;

SW_PLSQL(NULL)
------------------------------
do this

1 row selected.

Elapsed: 00:00:00.01
10g>  select sw_plsql(1) from dual;

SW_PLSQL(1)
------------------------------
do this

1 row selected.

Elapsed: 00:00:01.00

What does the community think?

Other uses for NULLIF

There are some features in the database that are understandably difficult to first come across. Unless you have a specific need to fulfil, or you aren't the type who meanders through the new features guide, or you're just relatively new to Oracle, you may not have come across useful day-to-day features & concepts such as WITH, DBMS_JOB or pipelining. (I just picked a few random ones that came to mind...)

There are also abundant single row functions that are available, and some of them are easy to use, but sometimes no apparent usage immediately stands out. I make it no secret that my favourite online reference manual is the Oracle Database SQL Reference. I have a shortcut to a local version in my browser toolbar. Personally I prefer the 10g layout to the 11g one, but beggars can't be choosers I suppose.

I thoroughly recommend beginners and even the more mature programmers to peruse this manual, in particular the chapter on SQL Functions. I believe awareness is often the key to writing a successful application - or at least not re-inventing the wheel.

So to stop beating around the bush, what about the NULL-related functions, specifically NULLIF. Have you used it?
The manual illustrates an example showing those employees whose job has changed since they were hired. If their job hasn't changed, then null is shown.

SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name;

LAST_NAME                 Old Job ID
------------------------- ----------
De Haan                   AD_VP
Hartstein                 MK_MAN
Kaufling                  ST_MAN
Kochhar                   AD_VP
Kochhar                   AD_VP
Raphaely                  PU_MAN
Taylor                    SA_REP
Taylor
Whalen                    AD_ASST
Whalen

What about if an employee record has multiple e-mail address columns - an alternative point of contact. In some scenarios/records this could potentially be unclean, so instead of coding with these older statements:

CASE WHEN email_address != email_address_alternate
THEN email_address_alternate
ELSE null
END email_address_alternate

DECODE(email_address, email_address_alternate, null, email_address_alternate) email_address_alternate

We can use a tidier:

NULLIF(email_address_alternate, email_address)

A similar situation applies to a mailing/delivery/home address (albeit ideally on a separate table) or a work/mobile/home number, but in this case the COALESCE function is more appropriate. I plan a blog entry on this function in the near future, along with performance considerations.

Perhaps you are performing a data load and you need to ignore certain indicative data coming through:

NULLIF(surname, 'INACTIVE')

The final scenario that I've started to use regularly is when faced with the old mathematical enigma of dividing by zero. (The wiki entry is a thrilling read, almost as fun as the images returned by google search)

Instead of facing this throughout a report:

select 1/0 from dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

We can do this to return null where price is 0 instead of an error.

SELECT (price-cost)/NULLIF(price,0) gp FROM sales;

Sweet.