11 October 2006

NVL == COALESCE == DECODE

Sometimes you can use many functions to achieve one goal. Today i saw a question on forums.oracle and i put it seperate here.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL>
SQL> drop table t;

Table dropped
SQL> create table t( a varchar2(1), b varchar2(1),c varchar2(1));
Table created
SQL>  INSERT INTO t VALUES ('a','b','c');
1 row inserted
SQL>   INSERT INTO t VALUES (null,'d','e');
1 row inserted
SQL>    INSERT INTO t VALUES (null, null, 'f');
1 row inserted
SQL>    INSERT INTO t VALUES (null, 'g', null);
1 row inserted
SQL>       INSERT INTO t VALUES ('h', null, 'i');
1 row inserted
SQL> SELECT * FROM t;
A B C
- - -
a b c
  d e
    f
  g
h   i

SQL> SELECT coalesce(a, b, c) FROM t;
COALESCE(A,B,C)
---------------
a
d
f
g
h

SQL> SELECT nvl(a, nvl(b, c)) FROM t;
NVL(A,NVL(B,C))
---------------
a
d
f
g
h

SQL> SELECT decode(a, NULL, decode(b, NULL, c, b), a) FROM t;
DECODE(A,NULL,DECODE(B,NULL,C,
------------------------------
a
d
f
g
h

SQL>

No comments: