SQL Functions: CASE/IF...THEN...ELSE Structure and Nesting

DECODE function:

The DECODE function allows for the implementation of the IF...THEN...ELSE or the CASE structure within SQL. The format is:

DECODE(column or expression, search value, resulting action [, search2 value, resulting action2,...][,default]

The following code checks or DECODES the city. If the city is Seekonk, then 100 is added to the yrgoal. If the city is Providence then 125 is added to the yrgoal. If the city is Fall River then 150 is added to the yrgoal The default, if there is a different city, is not to add anything to yrgoal. The result of the calculation is shown in the column that is labeled NEW_GOAL.

SQL CODE:

SQL> SELECT name, city, yrgoal,
  2  DECODE(city, 'Seekonk', yrgoal+100,
  3               'Providence', yrgoal+125,
  4               'Fall River', yrgoal+150,
  5                             yrgoal)
  6          NEW_GOAL
  7  FROM donor;

NAME            CITY          YRGOAL  NEW_GOAL
--------------- ---------- --------- ---------
Stephen Daniels Seekonk          500       600
Jennifer Ames   Providence       400       525
Carl Hersey     Providence
Susan Ash       Fall River       100       250
Nancy Taylor    Fall River        50       200
Robert Brooks   Fall River        50       200

6 rows selected.

In the code below, the yrgoal is multiplied by a different amount depending on the contact person. If a contact person other than the ones listed is encountered, the yrgoal is not changed and the current year goal is the NEW_GOAL.

SQL CODE:

SQL> SELECT name, yrgoal, contact,
  2  DECODE(contact, 'John Smith', yrgoal*1.1,
  3                  'Susan Jones', yrgoal*1.15,
  4                  'Amy Costa', yrgoal*1.2,
  5                  'John Adams', yrgoal*1.25,
  6                                yrgoal)
  7         NEW_GOAL
  8  FROM donor;

NAME               YRGOAL CONTACT       NEW_GOAL
--------------- --------- ------------ ---------
Stephen Daniels       500 John Smith         550
Jennifer Ames         400 Susan Jones        460
Carl Hersey               Susan Jones
Susan Ash             100 Amy Costa          120
Nancy Taylor           50 John Adams        62.5
Robert Brooks          50 Amy Costa           60

6 rows selected.

Nesting FUNCTIONS:

Functions can be nested one within another. The two examples I have chosen accomplish nothing worth while but illustrate nested functions. With nested functions, the inner function is evaluated first and then evaluation moves out with the outer function being evaluated last.

For example, a variety of date functions are used in this SELECT. The first thing that is done is to add 6 months to datefst using the ADD_MONTHS. Then the MONTHS_BETWEEN evaluates the number between the new datefst which had the 6 months added and the datefst. Finally the answer is converted to character using TO_CHAR.

SQL CODE:

1  SELECT TO_CHAR(MONTHS_BETWEEN(ADD_MONTHS(datefst,6),datefst)), datefst
  2* FROM donor
SQL> /

TO_CHAR(MONTHS_BETWEEN(ADD_MONTHS(DATEFS DATEFST
---------------------------------------- ---------
6                                        03-JUL-98
6                                        24-MAY-97
6                                        03-JAN-98
6                                        04-MAR-92
6                                        04-MAR-92
6                                        04-APR-98

6 rows selected.

In the example below, character functions are used. The first thing is to determine the length of state, the inner function. Next that length is used to take the substring starting with the 2nd (the length of state) and taking 4 characters. Finally the 4 characters are concatenated with the literal CITY=.

SQL CODE:

1  SELECT city, state, CONCAT('CITY=',SUBSTR(city,LENGTH(state),4))
  2* FROM DONOR
SQL> /

CITY       ST CONCAT('C
---------- -- ---------
Seekonk    MA CITY=eeko
Providence RI CITY=rovi
Providence RI CITY=rovi
Fall River MA CITY=all
Fall River MA CITY=all
Fall River MA CITY=all

6 rows selected.