Output not what was expected
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