Re: Output not what was expected

From: Philippe L. Balmanno <plb2862_at_cts.com>
Date: Sat, 21 Jul 2001 21:13:03 GMT
Message-ID: <3b2bf30d$0$774$e2e8da3_at_nntp.cts.com>


I've gotten the COMPUTE SUM LABEL Totals OF hours_logged ON employee_id and  COMPUTE SUM LABEL Totals OF dollars_charged ON employee_id to print by changing the employee_id to employee_name. However, the COMPUTE SUM LABEL 'Grand Totals' OF hours_logged ON REPORT and COMPUTE SUM LABEL 'Grand Totals' OF dollars_charged ON REPORT do not work. I can only assume that it doesn't like REPORT, could any tell me if this would be like an undefined variable problem? Thanks.

"Philippe L. Balmanno" <plb2862_at_cts.com> wrote in message news:3b2b9af5$0$770$e2e8da3_at_nntp.cts.com...
> OK, I found the date answer to the date section, wrong SQL used, TOCHAR vs
> TO_CHAR.
> On the second section for computations though I have tried the labels with
> single, double and no quotes. and the labels still do not print out. Does
> anyone have an idea? Thanks.
>
> "Philippe L. Balmanno" <plb2862_at_cts.com> wrote in message
> news: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:13:03 CEST

Original text of this message