Previous | Table of Contents | Next

Page 56

Listing 3.23Continued

3  where wages - 200 < 12000;
LAST_NAME       FIRST_NAME          WAGES
--------------- ---------------  --------
COOLIDGE        CALVIN               9500
JOHNSON         LYNDON              12000
JOHNSON         ANDREW               7500
KENNEDY         JOHN                11500
EISENHOWER      DWIGHT                  0

5 records selected.

Understanding Null Values and Expressions

Null means the value is unknown or blank. It does not mean the value equals zero or contains white space. This feature can cause many problems because Oracle does not perform calculations when one of the values is null. In Oracle, the result of a calculation with a null value is always unknown or null. Listing 3.24 illustrates a query that contains null values in the wages column. Notice the Eisenhower and the last Roosevelt records. The calculations were not made in these records. The reason is because the two records have a null value in their wages column. The result of a calculation with a null value is a null value. To correct this problem, you can use the NVL function discussed later in this chapter in its own section.

Listing 3.24L_03_25.TXT—Example of a Column Calculation when Null Values Exist

SQL> select last_name, wages, wages+500
2  from employee;
LAST_NAME           WAGES WAGES+500
--------------- --------- ---------
NIXON               12500     13000
KENNEDY             11500     12000
EISENHOWER
TRUMAN              11000     11500
ROOSEVELT           10400     10900
ROOSEVELT            8000      8500
ANTHONY              7000      7500
ROOSEVELT

.

19 records selected

Date Arithmetic

Date is a valid data format in SQL*PLUS. This means that the Oracle database actually stores the date as a numeric value. It is interpreted into a date that a user will understand when

Page 57

displayed. Because the date is stored as a numeric value, calculations can be performed on the column. Values can be added or subtracted from the date to produce another date, or two dates may be subtracted to determine the number of days between them. Listing 3.25 demonstrates several examples of the calculations that can occur.

Listing 3.25L_03_25.TXT—Examples of Various Date Calculations

SQL> select last_name, employment_date, birth_date,
2    (employment_date-birth_date)/365 "age at hire",
3    employment_date+100 "100 days", birth_date + (365*65) retirement
4  from employee;
LAST_NAME       EMPLOYMEN BIRTH_DAT age at hire 100 days  RETIREMEN
--------------- --------- --------- ----------- --------  ---------
COOLIDGE        07-AUG-21 01-JUL-72   50.93425  15-NOV-21  15-JUN-37
JOHNSON         23-NOV-63 27-AUG-08   55.276712 02-MAR-64 11-AUG-73
REAGAN          03-MAR-80 01-OCT-24   55.457534 11-JUN-80 15-SEP-89
BUSH            05-JAN-88 06-FEB-11   76.964384 14-APR-88 21-JAN-76
JOHNSON         13-APR-65 29-DEC-08   56.326027 22-JUL-65 13-DEC-73
CLINTON         01-JAN-92 03-APR-40   51.780822 10-APR-92 18-MAR-05
CARTER          10-JUL-76 14-JUL-13   63.032877 18-OCT-76 28-JUN-78

.

19 records selected

You might notice that the default date picture for a date is `01-JAN-97'. You can change this picture by using a date picture. Date pictures are discussed in the next chapter.

The time of day is also stored in the date value. One hour is equal to 1/24 of the day, and a minute is equal to 1/1440 of a date. To add 10 minutes to the time, just add 10/1440 to the date. The default time of day is midnight. The date value does not contain a fraction. The select statement in Listing 3.26 adds 6 hours and 20 minutes to the hire_date.

Listing 3.26L_03_26.TXT—Example of Adding Hours and Minutes to the Date

SQL> column a format a25
SQL> column b format a25
SQL> select last_name, to_char(employment_date, `Month dd YYYY HH:MI') a,
2     to_char(employment_date + 6/24 + 20/1440, `Month dd YYYY HH:MI') b
3  from employee;
LAST_NAME       A                         B
--------------- ------------------------- -------------------------
COOLIDGE        August    07 1921 12:00   August    07 1921 06:20
JOHNSON         November  23 1963 12:00   November  23 1963 06:20
REAGAN          March     03 1980 12:00   March     03 1980 06:20
BUSH            January   05 1988 12:00   January   05 1988 06:20
JOHNSON         April     13 1965 12:00   April     13 1965 06:20
                                                                          continues

Page 58

Listing 3.26Continued

CLINTON         January   01 1992 12:00   January   01 1992 06:20

.

19 records selected

Using Character Functions

SQL*PLUS provides the developer with a wealth of functions that he or she can use to manipulate character and date values, perform multi-row calculations (such as sums or averages), and convert column formats. These functions are used in the select and where clauses of the statement. Table 3.3 contains a description of the character functions.

Table 3.3Character Functions

Function Format Description
|| `string'||'string' Combines two strings.
ASCII ASCII(string) Returns the ASCII value of the
first character of the string.
CHR CHR(integer) Returns the character equivalent
to the ASCII value of the integer.
INITCAP INITCAP(string) Changes the first letter of the
string to uppercase.
INSTR INSTR(string, set [,BR> start [ , occurrence ] ]) Finds the location of the beginning
set of characters in the string.
LENGTH LENGTH(string) Returns the length of the string.
LOWER LOWER(string) Converts the entire string to
lower- case.
LPAD LPAD(string, length,
[, `set'])
Makes a string a specific length by
adding a specific set of characters
to the left of the string.
LTRIM LTRIM(string [, `set']) Trims the occurrences of any one of
a set of characters off the left side of a string.
RPAD RPAD(string, length, [, `set']) Makes a string a specific length
by adding a specified set of characters to the right.
RTRIM RTRIM(string [, `set']) Trims the occurrences of any one of
a set of characters off the right side of a string.

Page 59

Function Format Description
SOUNDEX SOUNDEX(string) Converts a name to a code value. It
is then used to compare names that might have small differences in
spelling but sound alike.
SUBSTR SUBSTR(string, start
[, count])
Extracts a piece of a string
beginning at start position
and counting for count characters from
start.
TRANSLATE TRANSLATE(string,
if, then)
Changes a string, character by
character, based on a
positional matching of characters in the if
string with characters in the
then string.
UPPER UPPER(string) Converts every letter in a string
into uppercase.

NOTE
In the following subsections, I discuss the major functions you are likely to use. You should gain an understanding of how to use all functions by these examples. Some of the functions such as chr and ascii are seldom used. These functions operate similar to any of the other functions. I don't feel it is necessary to illustrate these functions.

The Length Function

The length function is used to determine the actual length of a character string. This length is not the defined length of the column, but the part of the column filled with characters. In Listing 3.27, the column last_name is defined as char(15). Notice the values computed by the length command.

Listing 3.27L_03_27.TXT—Example of Using the Length Function

SQL> select first_name, length(first_name) from employee;
FIRST_NAME      LENGTH(FIRST_NAME)
--------------- ------------------
CALVIN                           6
LYNDON                           6
RONALD                           6
GEORGE                           6
ANDREW                           6

.

19 records selected.

Previous | Table of Contents | Next