Decode statement could be easily rewritten using the CASE statement : DECODE « Conversion Functions « Oracle PL / SQL






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

1.Syntax: DECODE
2.Decode char value to number
3.Decode for more than one key value pair
4.Use Decode as if statement to logic like 'if then else'
5.DECODE and GROUPING
6.Create a report that shows number of customers per state.
7.Use DECODE and SIGN functions on the ORD table
8.Extract any procedure, function or package
9.Decode the result from row_number over, partition by, order by
10.Decode a column with substr and instr
11.DECODE and substr function
12.Format nullable heading
13.DECODE in the GROUP BY clause
14.Demo range comparison with DECODE
15.Use decode as if statement and output 'high' or 'low'
16.Use decode create dynamic select statement
17.decode column data to output more meaningful information