0255-0256

Previous | Table of Contents | Next

Page 255

SELECT d.name                          dept_name,        d.dept_no                       dept_number,        e.first_name || e.last_name     emp_name,        e.job_title                     title,        e.hire_date                     start_date FROM dept    d,      emp     e WHERE d.dept_no   =  e.dept_no ORDER BY d.name, e.last_name; 

The next example shows the addition of the optional child table, called addresses:

SELECT d.name                                              dept_name,        d.dept_no                                           dept_number,        e.first_name || e.last_name                         emp_name,        e.job_title                                         title,        e.hire_date                                         start_date,        DECODE(a.box_number, NULL, a.adrs_1, a.box_number)  address,        DECODE(a.adrs_2, NULL, NULL, a.adrs_2)              address_2,        a.city || `, `||a.state ||'   `||a.zip              city_stat_zip FROM dept       d,      emp        e,      addresses  a WHERE d.dept_no   =  e.dept_no   AND e.adrs_id   =  a.adrs_id (+) ORDER BY d.name, e.last_name; 

An outer join (+) is used so that the employee row is still retrieved even if there is no address information available yet. The DECODEs retrieve the box number or adrs_1, depending on the existence of box number.

When you write INSERTs, UPDATEs, or DELETEs, be careful to ensure that the proper relationships exist within the subqueries. If you must manipulate each row from the query, you must use a cursor, which is discussed in Chapter 12, "PL/SQLA Procedural Language."

Additional Tips

This section is a summary of some of the information already given in this chapter, along with some new ideas.

When you compare DATE datatypes, it might be wise to truncate the dates (TRUNC hire_date) to ensure that the time component does not cause erroneous results. If the application enables the insertion of time into the DATE datatypes, then the inserted times are also taken into consideration when manipulating dates.

When writing SQL statements, null values are not considered by the database unless they are explicitly called for. In the following example, all rows for the employee table are retrieved except for one, the row containing the president:

SELECT e.first_name || e.last_name      emp_name,        z.first_name || z.last_name      manager FROM emp e,      emp z WHERE  z.emp_id = e.manager_id; 

Page 256

This is because the manager_id for the president is null. The following example shows how to retrieve the president along with the other employees .

SELECT e.first_name || e.last_name      emp_name,        z.first_name || z.last_name      manager FROM emp e,      emp z WHERE  z.emp_id = NVL(e.manager_id, e.emp_id); 

This code checks for a null manager ID. If the manager ID is null, the database returns the employee ID, which validates properly and returns a row for the president.

Summary

This chapter covered the major components of the SQL language, placing emphasis on functionality that is frequently used or misunderstood (such as the DECODE statement). It should be emphasized again that the key to writing good SQL is a complete understanding of the schema that the SQL is written against. With this understanding, you should have sufficient information to venture out and begin writing good SQL code.

Previous | Table of Contents | Next