Re: SQL*Plus reporting question

Jeff Busch (busch1_at_llnl.gov) wrote:
: I am attempting to write a SQL*Plus report which combines summary and
: non-summary data on the same line, but SQL*Plus doesn't like to combine
: these attributes.
 

: I want a report line that looks like this:
 

: Emp # Emp Name Dept Total Reg Hours Total OT Hours
: -----------------------------------------------------------------
: 1 Joe Blow MIS 4 2
: 2 Jane Doe HR 6 0
 

: The tables look like this:
 

: EMP EMP_HRS
: EMP_NO varchar2(3), EMP_NO varchar2(3),
: EMP_NAME varchar2(30), WORK_DATE date,
: EMP_DEPT varchar2(4) WORK_REG_HRS number,
: WORK_OT_HRS number
 

: Does anyone have any ideas on how to do this without using a temporary
: table?

: ---
: Jeff Busch Internet busch1_at_llnl.gov
: Voice (510) 423-9134 Fax (408) 263-1360

Use:

Select x.emp#, x.emp_name, x.dept,

       sum(y.regular_hours) Regular_hours,
       sum(y.Ot_hours) Total_Ot_hours

where x.emp_no = y.emp_no
  and y.date ......
group by x.emp#, x.emp_name, x.dept;

I hope the above query will serve your purpose.

Srini Kunamneni Received on Wed Jun 19 1996 - 00:00:00 CEST