The built-in SYSDATE function is used in the following example to return the current date from the database. Notice that the NEW_VALUE option of the COLUMN command is used to update the user variable report_date with the current value of SYSDATE as returned from the database.

COLUMN SYSDATE NEW_VALUE report_date
SELECT SYSDATE FROM DUAL;

SYSDATE is an Oracle built-in function that returns the current date and time. DUAL is a special Oracle table that always exists, always contains exactly one row, and always contains exactly one column. You could select SYSDATE from any other table, but DUAL works well because it returns only one row — all you need to set the date.

You may find that the date format returned by SYSDATE is not what you would prefer. It depends on the setting of the NLS_DATE_FORMAT parameter, which can vary from one database to the next. You can use the ALTER SESSION command to specify a different format, for example:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';

ALTER SESSION changes the format for the duration of the SQL*Plus session. Make sure you execute it prior to selecting SYSDATE from DUAL. Another alternative is to use the built-in TO_CHAR function to specify a format.

COLUMN current_date NEW_VALUE report_date
SELECT TO_CHAR(SYSDATE,'DD-Mon-YYYY') current_date FROM DUAL;

A column alias of current_date was used in this example to give a usable name to the date column, one that could be used easily with the COLUMN command.

Table 3.2 shows some typical date format strings that may be used with Oracle’s built-in TO_CHAR function or with the ALTER SESSION command.

You may or may not care whether the output of the SELECT SYSDATE statement appears on the display, but you can suppress it by using the SET TERMOUT command to toggle the display output off and then back on again. Here’s how to do that:

SET TERMOUT OFF
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';
COLUMN SYSDATE NEW_VALUE report_date
SELECT SYSDATE FROM DUAL;
SET TERMOUT ON

Finally, you need to add the date to either the report header or footer. Here’s an example of how to do that using the BTITLE command from the Project Hours and Dollars Detail report:

BTITLE LEFT `=============================================================' -
       SKIP 1 -
       LEFT report_date -
       RIGHT 'Page ' FORMAT 999 SQL.PNO

The addition of “LEFT report_date” to this BTITLE command causes the date to print left-justified on the same line as the page number. When you execute the report, the page footer will look like this:

=======================================================================
22-Feb-1998                                                   Page    1

In addition to the system date, this technique may be used to retrieve any other value from the database for inclusion in a report.

The syntax for the REPHEADER and REPFOOTER commands is the same. To define a report footer, simply use REPFOOTER in place of REPHEADER.

REPH[EADER] [[OFF|ON]|
            COL x|
            S[KIP] x|
            TAB x|
            LE[FT]|
            CE[NTER]|
            R[IGHT]|
            BOLD|
            FOR[MAT] format_spec|
            text|
            variable...]

The parameters you can use with REPHEADER and REPFOOTER are the same as, and work the same way as, those used with the TTITLE command.

One use for a report header is to define a report title that prints just on the first page of a report, leaving only column titles at the top of all subsequent pages. A report footer can be used to mark the end of a report, so you know for sure whether or not you have all the pages. Here, you will see an example showing how these things can be done.

Recall that the Project Hours and Dollars Report, the first one shown in this chapter, used the following commands to define page headers and footers:

TTITLE CENTER 'The Fictional Company' SKIP 3 -
       LEFT 'I.S. Department' -
       RIGHT 'Project Hours and Dollars Report' SKIP 1 -
       LEFT '=============================================+++============='

BTITLE LEFT '=============================================================' -
       SKIP 1 -
       RIGHT 'Page ' FORMAT 999 SQL.PNO

The TTITLE command defined a title containing the name of the report, which in this case was printed on each page of the report. By replacing TTITLE with REPHEADER, and adding a command to turn TTITLE off, the title containing the report name will print just once. The following example shows how this is done, and also defines a report footer:

TTITLE OFF
REPFOOTER CENTER '*** End of Hours and Dollars Report ***'
REPHEADER CENTER 'The Fictional Company' SKIP 3 -
       LEFT 'I.S. Department' -
       RIGHT 'Project Hours and Dollars Report' SKIP 1 -
       LEFT '============================================================='

BTITLE LEFT '=============================================================' -
       SKIP 1 -
       RIGHT 'Page ' FORMAT 999 SQL.PNO

The report footer, defined with the REPFOOTER command, will print on the last page, after the last detail record, to mark the end of the report. Here is what the Project Hours and Dollars report looks like when you run it using the heading commands shown in the previous example:

The Fictional Company                    
                                                             
                                                             
I.S. Department              Project Hours and Dollars Report
=============================================================
                                                      Dollars
Employee Name        Project Name          Hours      Charged
-------------------- -------------------- ------ ------------
Jonathan Gennick     Corporate Web Site       20    $3,380.00
Jonathan Gennick     Year 2000 Fixes          24    $4,056.00
Jonathan Gennick     Accounting System        24    $4,056.00
...
Bohdan Khmelnytsky   Corporate Web Site       20      $900.00
=============================================================
                                                    Page    1

                                                      Dollars
Employee Name        Project Name          Hours      Charged
-------------------- -------------------- ------ ------------
Bohdan Khmelnytsky   Year 2000 Fixes          24    $1,080.00
Bohdan Khmelnytsky   Accounting System        24    $1,080.00
...
Jacob Marley         TCP/IP                   16    $4,800.00
                     Implementation                          
                                                             
           *** End of Hours and Dollars Report ***           
...
=============================================================
                                                    Page    3

As you can see, the report title printed only on the first page of the report. Subsequent pages began with the column titles. The report footer printed on the last page, following the last detail line. Keep in mind when working with report headers and footers that these report elements still print within the context of a page. Page titles and footers print on each page, regardless of whether a report header or footer also prints on that page. Had the above report included a page title (TTITLE), the page title would have printed on the first page, prior to the report header.

A good example of this type or report would be one that fulfills the following request:

This is not an unusual type of request, not for me at least. The interesting thing about this request, though, is that every employee will need to be listed in exactly two categories. That means you would need to write a query that returned each employee record twice, in the correct categories.

When you are faced with this type of query, it can be helpful to simplify the problem by thinking in terms of separate queries, one for each category. It’s fairly easy to conceive of a query to bring back a list of employees that were on board at the beginning of the year. You just need to make sure the first of the year is between the hire and termination dates, and account for the fact that the termination date might be null. Here’s the query to return a list of people employed as of January 1, 1998:

SELECT employee_id,
       employee_name,
       employee_hire_date,
       employee_termination_date
  FROM employee
 WHERE employee_hire_date < TO_DATE('1-Jan-1998','dd-mon-yyyy')
   AND (employee_termination_date IS NULL
        OR employee_termination_date >= TO_DATE('1-Jan-1998','dd-mon-yyyy'))

This gives you the first section of the report — those employed at the beginning of the year. Retrieving the data for the remaining sections is a matter of using a different WHERE clause for each section. Table 3.3 shows the selection criteria for each section of the report.

WHERE employee_hire_date < TO_DATE('1-Jan-1998','dd-mon-yyyy')
   AND (employee_termination_date IS NULL
        OR employee_termination_date 
           >= TO_DATE('1-Jan-1998','dd-mon-yyyy'))

WHERE employee_hire_date >= TO_DATE('1-Jan-1998','dd-mon-yyyy')
   AND (employee_hire_date < TO_DATE('1-Jan-1999',
       'dd-mon-yyyy'))

WHERE employee_termination_date >= 
      TO_DATE('1-Jan-1998','dd-mon-yyyy')
  AND (employee_termination_date < 
       TO_DATE('1-Jan-1999','dd-mon-yyyy'))

WHERE employee_hire_date < TO_DATE('1-Jan-1999','dd-mon-yyyy')
   AND (employee_termination_date IS NULL
        OR employee_termination_date 
           >= TO_DATE('1-Jan-1999','dd-mon-yyyy'))

After separately developing the four queries, one for each section of the report, you can use SQL’s UNION operator to link those four queries together into one large query. There are four things to consider when doing this:

To be certain of getting all the records back from the query, use the UNION ALL operator to tie the queries together. Using UNION by itself causes SQL to filter out any duplicate rows in the result set. That’s not really an issue with this example — there won’t be any duplicate rows — but it’s an important point to consider.

In order to properly group the records, you can add a numeric constant to each of the four queries. For example, the query to return the list of those employed at the beginning of the year could return an arbitrary value of 1:

SELECT 1 sort_column,
         employee_id,
         employee_name,

The other queries would return values of 2, 3, and 4 in the sort column. Sorting the query results on these arbitrary numeric values serves two purposes. First, the records for each section of the report will be grouped together because they will all have the same constant. Second, the value of the sort column controls the order in which the sections print. Use a value of 1 for the section to be printed first, a value of 2 for the second section, etc.

The final thing to worry about is identifying the results to the reader of the report. The values used in the sort column won’t mean anything to the reader, so you also need to add a column with some descriptive text. Here’s how the final query for people employed at the beginning of the year looks with that text added:

SELECT 1 sort_column,
       'Employed at Beginning of Year' employee_status_text,
       employee_id,
       employee_name,
       employee_hire_date,
       employee_termination_date
  FROM employee
 WHERE employee_hire_date < TO_DATE('1-Jan-1998','dd-mon-yyyy')
   AND (employee_termination_date IS NULL
        OR employee_termination_date >= TO_DATE('1-Jan-1998','dd-mon-yyyy'))

The first column returned by this query is used to sort these records to the top of the report, while the second column serves to identify those records for the reader. The full-blown UNION query to produce all four sections of the report looks like this:

SELECT 1 sort_column,
       'Employed at Beginning of Year' employee_status_text,
       employee_id,
       employee_name,
       employee_hire_date,
       employee_termination_date
  FROM employee
 WHERE employee_hire_date < TO_DATE('1-Jan-1998','dd-mon-yyyy')
   AND (employee_termination_date IS NULL
        OR employee_termination_date >= TO_DATE('1-Jan-1998','dd-mon-yyyy'))
UNION ALL
SELECT 2 as sort_column,
       'Hired During Year' as employee_status_text,
       employee_id,
       employee_name,
       employee_hire_date,
       employee_termination_date
  FROM employee
 WHERE employee_hire_date >= TO_DATE('1-Jan-1998','dd-mon-yyyy')
   AND (employee_hire_date < TO_DATE('1-Jan-1999','dd-mon-yyyy'))
UNION ALL
SELECT 3 as sort_column,
       'Terminated During Year' as employee_status_text,
       employee_id,
       employee_name,
       employee_hire_date,
       employee_termination_date
  FROM employee
 WHERE employee_termination_date >= TO_DATE('1-Jan-1998','dd-mon-yyyy')
   AND (employee_termination_date < TO_DATE('1-Jan-1999','dd-mon-yyyy'))
UNION ALL
SELECT 4 as sort_column,
       'Employed at End of Year' as employee_status_text,
       employee_id,
       employee_name,
       employee_hire_date,
       employee_termination_date
  FROM employee
 WHERE employee_hire_date < TO_DATE('1-Jan-1999','dd-mon-yyyy')
   AND (employee_termination_date IS NULL
        OR employee_termination_date >= TO_DATE('1-Jan-1999','dd-mon-yyyy'))
ORDER BY sort_column, employee_id, employee_hire_date;

As you can see, the four queries have been unioned together in the same order in which the report is to be printed. That’s done for readability, though. It’s the ORDER BY clause at the bottom that ensures that the records are returned in the proper order.

All that’s left now that the query has been worked out is to follow the remaining steps in the report development methodology to format and print the report. To produce a fairly basic, columnar report, precede the query with the following commands:

--Setup pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55

--Set the linesize, which must match the number of equal signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 75

TTITLE CENTER 'The Fictional Company' SKIP 2 -
       CENTER 'Employee Turnover Report' SKIP 1 -
       LEFT '===================================' -
            '=======================================' -
       SKIP 3

--Format the columns
CLEAR COLUMNS
COLUMN sort_column NOPRINT
COLUMN employee_id NOPRINT
COLUMN employee_status_text HEADING 'Status' FORMAT A29
COLUMN employee_name HEADING 'Employee Name' FORMAT A20
COLUMN employee_hire_date HEADING 'Hire Date' FORMAT A11
COLUMN employee_termination_date HEADING 'Term Date' FORMAT A11

--Breaks and computations
BREAK ON employee_status_text SKIP 2 NODUPLICATES
CLEAR COMPUTES
COMPUTE NUMBER LABEL 'Total Count' OF employee_name ON employee_status_text

--Set the date format to use
ALTER SESSION SET NLS_DATE_FORMAT = 'dd-Mon-yyyy';

When you execute this report, the output will look like this:

The Fictional Company

                          Employee Turnover Report
==========================================================================


Status                        Employee Name        Hire Date   Term Date
----------------------------- -------------------- ----------- -----------
Employed at Beginning of Year Jonathan Gennick     15-Nov-1961
                              Jenny Gennick        16-Sep-1964 05-May-1998
                              Jeff Gennick         29-Dec-1987 01-Apr-1998
                              Pavlo Chubynsky      01-Mar-1994 15-Nov-1998
                              Taras Shevchenko     23-Aug-1976
                              Hermon Goche         15-Nov-1961 04-Apr-1998
***************************** --------------------
Total Count                                      6


Hired During Year             Horace Walker        15-Jun-1998
                              Bohdan Khmelnytsky   02-Jan-1998
                              Ivan Mazepa          04-Apr-1998 30-Sep-1998
                              Jacob Marley         03-Mar-1998 31-Oct-1998
***************************** --------------------
Total Count                                      4


Terminated During Year        Jenny Gennick        16-Sep-1964 05-May-1998
                              Jeff Gennick         29-Dec-1987 01-Apr-1998
                              Pavlo Chubynsky      01-Mar-1994 15-Nov-1998
                              Ivan Mazepa          04-Apr-1998 30-Sep-1998
                              Hermon Goche         15-Nov-1961 04-Apr-1998
                              Jacob Marley         03-Mar-1998 31-Oct-1998
***************************** --------------------
Total Count                                      6


Employed at End of Year       Jonathan Gennick     15-Nov-1961
                              Horace Walker        15-Jun-1998
                              Bohdan Khmelnytsky   02-Jan-1998
                              Taras Shevchenko     23-Aug-1976
***************************** --------------------
Total Count                                      4

That’s all there is to it. It wouldn’t be a big leap to turn this report into a master/detail report, with each section starting on a new page. Using this technique, you can develop similar reports with any number of sections you need.