How to use SQL DECODE

This is a basic example on how to use the SQL function DECODE.
Let's consider a table where either one of the two fields FIELD1 and FIELD2 is not null. It never happens that both are null, or both not null.
The desired outcome is to display the value of FIELD1 when FIELD1 is not null, and the value of FIELD2 when it's not null. The DECODE function is the simplest way to achieve this. Let's see how to use it.

First, we create the table for the purpose of our demonstration, and populate it with a few records.

CREATE TABLE TWO_FIELDS (ID NUMBER, 
FIELD1 VARCHAR2(10), 
FIELD2 VARCHAR2(10));


INSERT INTO TWO_FIELDS VALUES (1, 'F1-1', NULL);
INSERT INTO TWO_FIELDS VALUES (2, NULL, 'F2-1');
COMMIT;

Let's now select some data according to our requirement, using the DECODE function:

SELECT ID
, FIELD1
, FIELD2
, DECODE(FIELD1, NULL, FIELD2, FIELD1) 
FROM TWO_FIELDS

...The DECODE function can be read like this: "if field1 is null, then display field2, otherwise display field1". Note that it is possible to enforce the rule with a constraint "either FIELD1 or FIELD2 is not null":

ALTER TABLE TWO_FIELDS 
ADD CONSTRAINT C1 
CHECK(FIELD1 IS NOT NULL OR FIELD2 IS NOT NULL);

... or (better) like this:

ALTER TABLE TWO_FIELDS 
ADD CONSTRAINT C2 
CHECK(
  (FIELD1 IS NOT NULL AND FIELD2 IS NULL)
  OR 
  (FIELD1 IS NULL AND FIELD2 IS NOT NULL)
)