How to use the CASE clause in a SQL statement (a demo)

This is a little demo to show how to use the CASE clause in a SQL statement:

drop table two_fields;

drop table two_fields2;

create table two_fields (id number, text varchar2(200), field1 varchar2(10), field2 varchar2(10));

create table two_fields2 (id number, text varchar2(200), field12 varchar2(10));

insert into two_fields values (1, 'text1', 'f1-1', null);
insert into two_fields values (2, 'text2', 'f1-2', null);
insert into two_fields values (3, 'text3', 'f1-3', null);
insert into two_fields values (4, 'text4', null, 'f2-1');
insert into two_fields values (5, 'text5', null, 'f2-2');
insert into two_fields values (6, 'text6', null, 'f2-3');

commit;

select * from two_fields;

select * from two_fields2;

select id, text, field1, field2, case field1 when null then 'field2' else 'field1' end as f1f2 from two_fields;

select id, text, field1, field2, decode (field1 , null, field2, field1) f1t from two_fields;

INSERT INTO TWO_FIELDS2 (SELECT ID, TEXT,  decode (field1 , null, field2, field1) from two_fields);


ALTER TABLE two_fields ADD CONSTRAINT c1 check (field1 is not null or field2 is not null);

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))