Using Scalar Functions for Retrieving Data

How to use scalar functions

Figure 3-7 introduces you to scalar functions, which operate on a single value and return a single value. These functions work differently than the aggregate functions described in chapter 5 that are used to summarize data. For now, don’t worry about the details of how these functions work, because you’ll learn more about them in chapter 8. Instead, just focus on how they’re used in column specifications.

To code a function, you begin by entering its name followed by a set of parentheses. If the function requires one or more parameters, you enter them within the parentheses and separate them with commas. When you enter a parameter, you need to be sure it has the correct data type.

The first example in this figure shows how to use the SUBSTR function to extract the first character of the vendor_contact_first_name and vendor_contact_last_name columns. The first parameter of this function specifies the column name; the second parameter specifies the starting position; and the third parameter specifies the number of characters to return. The results of the two functions are then concatenated to form initials, as shown in the result set for this statement.

The second example shows how to use the TO_CHAR function. This function converts a column with a DATE or NUMBER data type to a character string. A common use for it is in concatenation operations, where all the data being concatenated must be string data. This function has two parameters. The first parameter, which specifies the column name, is required. The second parameter, which specifies a format mask for the column, is optional. In this example, a format mask of ‘MM/DD/YYYY’ is used to convert the payment_date column from a DATE type to a CHAR type. This format mask specifies that the date should be displayed with a two-digit month, followed by a forward slash, followed by a two-digit day, followed by another forward slash, followed by a four-digit year.

In the second example, the payment_date column for Invoice # P-0608 is NULL. Note that this causes the TO_CHAR function to return an empty string for the payment date.

The third example uses the SYSDATE function to return the current date. Since this function doesn’t accept any parameters, you don’t need to code any parentheses after the name of the function. In fact, if you do code parentheses after the name of the function, you will get an error when you execute the statement. In this example, the second column uses the SYSDATE function to return the current date, and the third column uses the SYSDATE function to calculate the number of days between the two dates. Here, the third column also uses the ROUND function to round the decimal value that’s returned by the calculation to an integer.

The fourth example shows how to use the MOD function to return the remainder of a division of two integers. Here, the second column contains an expression that returns the remainder of the division operation when the invoice-id column is divided by 10. In the result set, you can see the results for IDs 9 through 11 (the remainders are 9, 0, and 1).

A SELECT statement that uses the SUBSTR function

SELECT vendor_contact_first_name, vendor_contact_last_name,
SUBSTR(vendor_contact_first_name, 1, 1) ||
SUBSTR(vendor_contact_last_name, 1, 1) AS initials
FROM vendors

A SELECT statement that uses the TO_CHAR function

SELECT 'Invoice: # ' || invoice_number || ', dated ' ||
TO_CHAR(payment_date, 'MM/DD/YYYY') ||
' for $' || TO_CHAR(payment_total) AS "Invoice Text"
FROM invoices

A SELECT statement that uses the SYSDATE and ROUND functions

SELECT invoice_date,
SYSDATE AS today,
ROUND(SYSDATE - invoice_date) AS invoice_age_in_days
FROM invoices

A SELECT statement that uses the MOD function

SELECT invoice_id,
MOD(invoice_id, 10) AS Remainder
FROM invoices
ORDER BY invoice_id

Description

1. A SQL statement can include a function. A function performs an operation and returns a value.
   
2. For more information on using functions, see chapter 8.

--------------------------------------------Figure 3-7 How to use scalar functions