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>
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:
Post a Comment