Output not what was expected

From: Philippe L. Balmanno <plb2862_at_cts.com>
Date: Sat, 21 Jul 2001 21:11:24 GMT
Message-ID: <3b255d6f$0$99816$e2e8da3_at_nntp.cts.com>


I am still learning, this time from Oreilly's "Oracle SQL*Plus The Definitive Guide," I finished Oracle University's series for OCP DBA (8i). I have come up to a situation where the script I wrote (below, to produce a report) looks simular to the publication however, neither will print the labels in the *** Breaks and computations *** section. Secondly, although I'm using the authors method for the *** Inserting a date into the report
*** section, it doesn't work either.

Would someone please advise me, I've read both publications again and cannot seem to find the section dealing with these problems. Thank you.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option
JServer Release 8.1.7.0.0 - Production

REM             *** Set pagesize parameters ***
SET               NEWPAGE 0
SET               PAGESIZE 55


REM             *** Set the Line size for both titles and report ***
SET               LINESIZE 77

REM             *** Eliminate the blank lines between records ***
SET               RECSEP OFF


REM              *** Inserting a date into the report ***
SET               TERMOUT OFF
ALTER          SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';
COLUMN     current_date NEW_VALUE report_date
SELECT        TOCHAR(SYSDATE, 'DD-Mon-YYYY') current_date FROM DUAL;
SET               TERMOUT ON


REM              *** Setup page header and footer employee (new)values in
the heading ***
TTITLE          CENTER 'LinkIT' SKIP 3 -
                      LEFT 'I.T. department' -
                      RIGHT 'Project Hours and Dollars Report' SKIP 1 -
                      LEFT '========================================' -
                                '===============================' -
                      SKIP 2 'Employee: ' Format 9999 emp_id_var ' '
emp_name_var SKIP 3
BTITLE         LEFT '========================================' -
                                '==============================' -
                      SKIP 1 -
                      LEFT 'Printed for: ' FORMAT A20 SQL.USER -
                      CENTER 'on: ' report_date -
                      RIGHT 'Page ' FORMAT 999 SQL.PNO


REM              *** Format the columns and output & declare new values***
COLUMN     employee_id NEW_VALUE emp_id_var NOPRINT
COLUMN     employee_name NEW_VALUE emp_name_var NOPRINT
COLUMN     project_id HEADING '     Proj ID' FORMAT 9999
COLUMN     project_name HEADING 'Project Name' FORMAT A26 WORD_WRAPPED
COLUMN     time_log_date HEADING 'Date' FORMAT A11
COLUMN     hours_logged HEADING 'Hours' FORMAT 9,999
COLUMN     dollars_charged HEADING 'Dollars|Charged' FORMAT $999,999,999.99


REM              *** Breaks and computations  ***
BREAK ON  employee_id SKIP PAGE NODUPLICATES
              ON  employee_name NODUPLICATES
              ON  project_id SKIP 2 NODUPLICATES
              ON  project_name NODUPLICATES
CLEAR COMPUTES
COMPUTE SUM LABEL 'Project Totals' OF hours_logged ON project_name
COMPUTE SUM LABEL 'Project Totals' OF dollars_charged ON project_name
COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id
COMPUTE SUM LABEL 'Grand Totals' OF hours_logged ON REPORT
COMPUTE SUM LABEL 'Grand Totals' OF dollars_charged ON REPORT


REM            *** Turn off feedback and set TERMOUT off|no scrll to screen

***
SET FEEDBACK OFF SET TERMOUT OFF SPOOL C:\oracle\reports\prjhrs$4.txt REM *** Execute the query statement and generate report *** SELECT e.employee_id, e.employee_name, p.project_id, p.project_name, TO_CHAR(ph.time_log_date, 'dd-Mon-yy') time_log_date, ph.hours_logged, ph.dollars_charged FROM employee e, project p, project_hours ph WHERE e.employee_id = ph.employee_id AND p.project_id = ph.project_id ORDER BY e.employee_id, p.project_id, ph.time_log_date; REM *** Reset and clear settings for the next report *** SPOOL OFF SET TERMOUT ON SET FEEDBACK ON CLEAR COLUMNS CLEAR COMPUTES
Received on Sat Jul 21 2001 - 23:11:24 CEST

Original text of this message