Decode statement could be easily rewritten using the CASE statement
SQL>
SQL> CREATE TABLE myCar
2 (
3 MAKER VARCHAR (25),
4 MODEL VARCHAR (25),
5 PRICE NUMERIC
6 );
Table created.
SQL>
SQL> INSERT INTO myCar VALUES('CHRYSLER','CROSSFIRE',33620);
1 row created.
SQL> INSERT INTO myCar VALUES('CHRYSLER','300M',29185);
1 row created.
SQL> INSERT INTO myCar VALUES('HONDA','CIVIC',15610);
1 row created.
SQL> INSERT INTO myCar VALUES('HONDA','ACCORD',19300);
1 row created.
SQL>
SQL> INSERT INTO myCar VALUES('FORD','MUSTANG',15610);
1 row created.
SQL> INSERT INTO myCar VALUES('FORD','LATESTnGREATEST',NULL);
1 row created.
SQL> INSERT INTO myCar VALUES('FORD','FOCUS',13005);
1 row created.
SQL>
SQL> SELECT
2 model,
3 DECODE(maker, 'CHRYSLER','DOMESTIC'
4 ,'FORD','DOMESTIC'
5 ,'FOREIGN')
6 FROM myCar;
MODEL DECODE(M
------------------------- --------
CROSSFIRE DOMESTIC
300M DOMESTIC
CIVIC FOREIGN
ACCORD FOREIGN
MUSTANG DOMESTIC
LATESTnGREATEST DOMESTIC
FOCUS DOMESTIC
7 rows selected.
SQL>
SQL> SELECT
2 model,
3 CASE maker
4 WHEN 'CHRYSLER' THEN 'DOMESTIC'
5 WHEN 'FORD' THEN 'DOMESTIC'
6 ELSE 'FOREIGN'
7 END
8 FROM myCar;
MODEL CASEMAKE
------------------------- --------
CROSSFIRE DOMESTIC
300M DOMESTIC
CIVIC FOREIGN
ACCORD FOREIGN
MUSTANG DOMESTIC
LATESTnGREATEST DOMESTIC
FOCUS DOMESTIC
7 rows selected.
SQL>
SQL> drop table myCar;
Table dropped.
Related examples in the same category