Mega Code Archive
COALESCE(state, NA)
<![CDATA[
SQL>
SQL> CREATE TABLE publishers(
2 pub_id CHAR(3) NOT NULL,
3 pub_name VARCHAR(20) NOT NULL,
4 city VARCHAR(15) NOT NULL,
5 state CHAR(2) NULL ,
6 country VARCHAR(15) NOT NULL);
Table created.
SQL>
SQL> INSERT INTO publishers VALUES('P01','A','New York','NY','USA');
1 row created.
SQL> INSERT INTO publishers VALUES('P02','C','San Francisco','CA','USA');
1 row created.
SQL> INSERT INTO publishers VALUES('P03','S','Hamburg',NULL,'Germany');
1 row created.
SQL> INSERT INTO publishers VALUES('P04','T','Berkeley','CA','USA');
1 row created.
SQL>
SQL> SELECT
2 pub_id,
3 city,
4 COALESCE(state, 'N/A') AS "state",
5 country
6 FROM publishers;
PUB CITY sta COUNTRY
--- --------------- --- ---------------
P01 New York NY USA
P02 San Francisco CA USA
P03 Hamburg N/A Germany
P04 Berkeley CA USA
SQL>
SQL> drop table publishers;
Table dropped.
SQL>
]]>