NVL Tips: Is it possible to run a query if the result of a first one is null?

Sometimes we are presented cases where only complex solutions come to our minds. This time NVL provides an easy answer to what might look a much difficult situation.

TABLE1

mydate,
status,
account

TABLE2

mydate,
location,
account

If this query is null:
select max(mydate) from TABLE1 where status = 'B' and account = '123';

Then run this one
select max(mydate) from TABLE2 where location in('a','b','c') and account = '123';

Here is an example using NVL

select
nvl(
(select max(mydate) from TABLE1 where status = 'B' and account = '123'),
(select max(mydate) from TABLE2 where location in('a','b','c') and account = '123')
)
from dual;

This is what it would look like when you have multiple rows:

TABLE3

account,
status

SELECT c.account,
nvl(
(select max(mydate) from TABLE1 A where status = 'B' and account = c.account),
(select max(mydate) from TABLE2 B where location in('a','b','c') and account = c.account)
)
FROM table3 C
WHERE
c.status='A'
.
.
. ;

As you can see this is a very easy example using the NVL, of course it has its constraints, for example when selecting more than one value from the query, but still is a powerful use for the NVL.

Hope it helps!!

Advertisements
This entry was posted in SQL and tagged , , , , , . Bookmark the permalink.

2 Responses to NVL Tips: Is it possible to run a query if the result of a first one is null?

  1. gm says:

    NVL always evaluates both arguments, so even if “where status = ‘B’ and account = c.account” is not null it will evaluate “where location in(‘a’,’b’,’c’) and account = c.account”

  2. gm says:

    About NULLIF.

    SQL> SELECT NULLIF(TRUNC(dbms_random.value(0, 2)), 0) FROM dual;
    NULLIF(TRUNC(DBMS_RANDOM.VALUE(0,2)),0)

    Trace Event 10053:
    Final query after transformations:******* UNPARSED QUERY IS *******
    SELECT CASE WHEN TRUNC(“SYS”.”DBMS_RANDOM”.”VALUE”(0,2))=0 THEN NULL ELSE TRUNC(“SYS”.”DBMS_RANDOM”.”VALUE”(0,2)) END “NULLIF(TRUNC(DBMS_RANDOM.VALUE” FROM “SYS”.”DUAL” “DUAL”

    The same expression evaluates twice!!!

    so just Use CASE/DECODE or COALESCE!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s