Additional functions

DECODE Function

The DECODE function is an Oracle extension to ANSI SQL. The DECODE function enables you to use If-Then-Else logic when displaying values. The general format is:
DECODE(Expression, Search1, Result1, Search2, Result2, ..., Else Default)
The expression can be a column value of any data type, or a result from some type of computation or function. The expression is compared to search1 and if expression = search1, then result1 is returned. If not, then the search continues to compare expression = search2 in order to return result2, etc. If the expression does not equal any of the search values, then the default value is returned. The else default can be a column value or the result of some type of computation or function.

Suppose that the senior project manager requests a listing that highlights employee work activities for project 4. Employees who work more than 30 hours on a project are to be designated as having "worked very hard" while others "worked ok." The SELECT statement in SQL Example 10.30 uses the DECODE function to produce the desired result table. The TRUNC function truncates the result of the HoursWorked column divided by 30. For employees working 30 or more hours, this yields a truncated value that is greater than or equal to 1. The DECODE function tests for a value of zero (0). The TRUNC function yields zero for employees working less than 30 hours and displays the appropriate message in the Work Status column of the result table.

/* SQL Example 10.30 */

COLUMN "Employee ID" FORMAT A11;

SELECT EmployeeID "Employee ID", HoursWorked "Hours Worked",

DECODE(TRUNC(HoursWorked/30), 0, 'Worked OK', 'Worked Very Hard')

"Work Status"

FROM ProjectAssignment

WHERE ProjectNumber = 4;
Employee ID Hours Worked Work Status

----------- ------------ ----------------

23100 10.3 Worked OK

33344 5.1 Worked OK

66432 19.2 Worked OK

88505 34.5 Worked Very Hard

SUMMARY

In this chapter, you learned numerous additional functions that add power to SQL queries. Character functions are used to manipulate CHAR data. The UPPER, LOWER, and INITCAP functions alter the appearance of information displayed in a result table by displaying data as all upper- or lowercase letters, or by capitalizing the first letter of a string. The LEN function computes the number of characters in a string while the SUBSTR function extracts a substring form a character string. The LTRIM and RTRIM functions trim character strings of any leading and trailing blank characters while the INSTR function searches for a substring within a larger string.

Number functions manipulate NUMBER data. There are numerous transcendental functions such as ACOS and ASIN used for scientific applications. The NVL function is used to substitute values where data are otherwise NULL in value. The ABS function can be used to compute absolute differences between two values. The POWER and SQRT functions are used to raise numeric values to specified exponential values and to compute square roots, respectively. The ROUND and TRUNC (truncate) functions display numeric values to specific levels of mathematical precision. The GREATEST and LEAST functions extract the largest and smallest values from a group of columns.

The functions TO_CHAR and TO_DATE are used to convert data values to VARCHAR2 and to DATE data types, respectively. These functions can also be used to format date and time output in a result table and to format data for row insertions when combined with the INSERT command. The SYSDATE function returns the current system date and time. The ADD_MONTHS function adds the specified number of months to a specified date in order to return a specific date in the future as part of a query.

The DECODE function is an Oracle extension to standard ANSI SQL and enables the use of If-Then-Else logic to display values.

REVIEW EXERCISES

Learn These Terms

1. ABS—computes the absolute value of a number or numeric expression.

2. ADD_MONTHS—adds the specified number of months to the specified date and returns that date.

3. GREATEST—extracts the largest (greatest) value from a group of columns.

4. INITCAP—capitalizes the first letter of a string of characters.

5. INSTR—searches a character string for a character string subset and returns the start position and/or occurrence of the substring.

6. LEAST—extracts the smallest (least) value from a group of columns.

7. LENGTH—returns a numeric value equivalent to the number of characters in a string of characters.

8. LOWER—returns a character value that is all lowercase.

9. LTRIM—trims specified characters from the left end of a string.

10. NVL—a substitution function that allows a programmer to substitute a specified value wherein stored values are NULL.

11. POWER—raises a numeric value to a specified positive exponent.

12. ROUND—rounds a value to a specified number of digits of precision.

13. RTRIM—trims specified characters from the right end of a string.

14. SQRT—computes the square root of a numeric value, expression or NUMBER column value.

15. SUBSTR—returns a string of specified length from a larger character string beginning at a specified character position.

16. SYSDATE—returns the current system date and time.

17. TO_CHAR—converts a date value to a character string.

18. TO_DATE—converts a character string or number to a date value.

19. TRUNC—truncates digits from a numeric value.

20. UPPER—returns a character value that is all uppercase.

Concepts Quiz

1. Are numbers such as telephone numbers and product numbers treated as character or numeric data? Why?

2. What is the purpose of the UPPER, LOWER, and INITCAP functions?

3. What does the LENGTH function do?

4. What is the purpose of the SUBSTR function? What will the following SELECT statement do?

SELECT SUBSTR(SSN,6,4)

FROM Employee;

5. What is the purpose of the LTRIM and RTRIM functions?

6. When using the INSTR function to search a character string, what does the function return if the character string is found? Not found?

7. Is the NVL function limited to use with the NUMBER data type?

8. Which function would you use to measure the magnitude by which a particular value deviates from some standard or average value?

9. How do the GREATEST and LEAST functions differ from the aggregate functions MAX and MIN?

10. What values are turned by the SYDATE function?

11. What is the standard format for date values in Oracle?

12. What value is returned by the following expression: SYSDATE + 1

13. You wish to know the date six months from now. Which function is best for displaying this value?

14. What is the purpose of the TO_CHAR and TO_DATE functions?

15. How does the ROUND function work with date values?

16. Which function provides If-Then-Else logic?

SQL Coding Exercises and Questions

In answering the SQL exercises and questions, submit a copy of each command that you execute and any messages that Oracle generates while executing your SQL commands. Also list the output for any result table that is generated by your SQL statements.
1. Management requires a listing of employees by last name, first name, and middle initial for department number 4. The last name should be displayed in all capital letters. The entire name should be concatenated together so as to display in a single field with a column heading of "Employee Name." The rows should be sorted by employee last name, then employee first name.

2. Write a query that displays the department name and the length in number of characters of each department's name. Use the department table. Label the column headings appropriately.

3. Rewrite the query for question 1 to list employees from every department, but only those employees with the character string "on" in the name.

4. Management wants a listing of department numbers and names (use the department table)—display the output as a single column with the heading "Department Information" – convert the DepartmentNumber column to character data as part of the query.

5. Write a query that displays the first four characters of each employee's last name and the last four digits of each employee's SSN for department 8. Label the column headings "Name" and "SSN." Order the result table rows by employee last name.

6. Create the table named MonthTable that is discussed in the chapter. Add the first six months of the year to the table. Write a SELECT statement to display the month name, untrimmed month name length, and trimmed month name length as was done in the chapter.

7. Write a query to display a listing of employee last names and the EmployeeID of each employee's supervisor for the employees working in department 8. If the employee has no supervisor, display the message "Top Supervisor." Provide appropriate headings. Sort the result table by employee last name.

8. Develop a listing for the company's senior project manager that lists employees that reported working between 15 and 25 hours (inclusive) on assigned projects. List the employee last name, project number, and hours worked. Use the ABS function. Do NOT use the BETWEEN operator or any logical operator. Join the tables by use of the FROM clause. Use meaningful column headings. Sort the rows of the result table by employee last name. HINT: 20 is the midpoint between 15 and 25 hours, the absolute value of the difference would be 5 hours.

9. The senior project manager needs a listing by employee last name, project number, and hours worked (HoursWorked column) rounded to the nearest integer value for projects 3 and 8. Join the tables by use of the FROM clause. Sort the result table by employee last name within project number. Use meaningful column names.

10. Write a query to display information about male dependents for the human resources manager. Display each dependent's name, gender, and date of birth. The date of birth should be displayed as: Month Name (spelled out), two-digit day, and four-digit year (e.g., December 05, 1970). Use the COLUMN commands shown here to format the first two columns of output.

COLUMN "Gender" Format A6;

COLUMN "Dep Name" FORMAT A15;

11. In question 10, the month values are displayed blank-padded to nine characters in length. Rewrite the query by using the RTRIM function and concatenation operators to display the birth date column by removing the blank-padding for the month part of the string of characters forming the date.

12. Write a query to display each dependent's name, date of birth, and date on which the dependent turned or will turn 65 years of age, but only for dependents born after January 1, 1980. Use meaningful column names. Display each date using the DD-MON-YYYY format. Use the ADD_MONTHS, TO_CHAR, and TO_DATE functions. Hint: 65 years equals 780 months.

13. Write a short query to display the current day of the week spelled out, for example Monday or Wednesday. The value should be obtained from the operating system internal date.

14. The human resources manager needs a listing of dependents including their name and gender, but only for dependents that are spouses. Instead of displaying the coded values for gender, the result table must display the terms "Male" and "Female," as appropriate. Use meaningful column headings. Sort the result table by dependent name.

15. Write a query that displays the scientific medicine name and the length in number of characters of each medicine name. Use the medicine table. Label the column headings appropriately. You only need to provide the first five rows of the result table in your laboratory report. How many rows are in the result table?

16. Rewrite the query for question 3 to list employees where the character strings of "OR" or "RO" are either capitalized or lower case, or a combination of the two in the employee's name. Use the UPPER function.

17. The hospital’s Chief of Administration wants a listing of rooms and bed numbers. Display the RoomNumber and BedNumber columns from the bed table as a single column with the heading Rooms/Beds. Order the output by RoomNumber. You only need to provide the first five rows of the result table in your laboratory report. How many rows are in the result table?

18. Write a query to display a listing of employee last names, title, and salary for employees with a title of either 'Building Custodian' or 'L.P.N.'. If the employee is paid a wage, the salary will be NULL. In this situation, display the value $0.00. Provide appropriate headings. Sort the result table by staff member last name.

19. Write a query to compare treatment charges to standard service charges. Display the ServiceID and StandardCharge columns from the service table, and the ChargeAmount column from the treatment table. Only display the value if the difference between the service and actual charge is more than $50.00 in difference (either high or low). Also display a computed column that is the difference between the service and actual charge (as a positive number). Use appropriate column sizes and headings. Join the tables by use of the FROM clause.

20. Modify the query for question 19 to display any rows where there is any difference between the service and actual charge, but only where the actual charge is less than the service charge. Order the output by differences from largest to smallest.

21. List the ServiceID and StandardCharge for services and round the StandardCharge to the nearest $10 dollars, but only for services costing $30 or less. Order the output by ServiceID. List all rows produced for the result table in your laboratory report. A sample result table looks similar to this:

Service Rounded Charge

------- --------------

10060 $260.00

10061 $320.00

10120 $230.00

22. Write a query to display employee information for the human resources manager. Display each employee's last and first name (concatenated), hospital title, and date hired for employees with a title that begins with the capital letter "R". The date hired should be displayed as: Month Name (spelled out), two-digit day, and four-digit year, e.g., December 05, 2002. Use the COLUMN commands shown here to format the first two columns of output. Sort the output by date hired.
COLUMN "Hospital Title" Format A20;

COLUMN "Employee Name" FORMAT A30;

23. The month values produced by your solution to question #12 are displayed blank-padded to nine characters in length. Use the RTRIM function with concatenation operators to rewrite the query to display the date hired column without blank-padding for the month part of the string of characters forming the date.

24. Write a query to display each doctor's name (last and first), date hired, and date on which the doctor will celebrate 10 years of employment with the hospital. Do not list doctors who have already had their 10 year employment anniversary (Hint: Use SYSDATE). Doctors have a title of "M.D." or a title that begins with "M.D." Use meaningful column names. Display each date using the DD-MON-YYYY format. Use the ADD_MONTHS and TO_CHAR functions. Hint: 10 years equals 120 months. Order the rows in the result table by the date hired.

25. Write a query to display each patient's name (last and first), date of birth, and date on which the patient will be 60 years old. Use meaningful column names. Display each date using the DD-MON-YYYY format. Use the ADD_MONTHS and TO_CHAR functions. Hint: 60 years equals 720 months. Order the rows in the result table by the patient's date of birth from the oldest to the youngest patient You only need to provide the first five rows of the result table in your laboratory report. How many rows are in the result table?

26. The hospital's patient census manager needs a listing of beds by room and whether or not a bed is available or occupied based on the value of the Availability column of the bed table, but only for beds located in the emergency room (the RoomNumber column value begins with the letters "ER"). Instead of displaying the coded values for the Availability column, your result table must display the terms "Occupied" and "Available," as appropriate. Use meaningful column headings. Sort the result table by BedNumber within RoomNumber. The result table will display the RoomNumber, BedNumber, and Availability columns.

27. Modify the query you wrote for question 26 to include the Description column from the bedClassification table.

28. Modify the query you wrote for question 27 to only list emergency room beds that have as part of the Description the word "fixed" (upper or lower case or any combination).

revised October 18, 2007