Single-row
functions can be nested to any depth. Nested functions are evaluated from the
innermost level to the outermost level. Some examples follow to show you the
flexibility of these functions.
SELECT
last_name,
UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8),
'_US'))
FROM employees
WHERE department_id = 60;
The example
displays the last names of employees in department 60. The evaluation of the
SQL statement involves three steps:
1. The inner function retrieves the first
eight characters of the last name.
Result1 = SUBSTR
(LAST_NAME, 1, 8)
2. The outer function concatenates the
result with _US.
Result2 = CONCAT(Result1, '_US')
3. The outermost function converts the
results to uppercase.
The entire
expression becomes the column heading because no column alias was given.
Example:
Display the
date of the next Friday that is six months from the hire date. The resulting
date should appear as Friday, August 13th, 1999. Order the results by hire
date.
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS
(hire_date, 6), 'FRIDAY'),
'fmDay, Month ddth, YYYY')
"Next 6 Month Review"
FROM employees
ORDER BY hire_date;
