Re: SQL*Plus reporting question

In article <4q93pp$go8_at_lll-winken.llnl.gov>, busch1_at_llnl.gov (Jeff Busch) writes:

> 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?

Hi Jeff,

you can avoid the temp table by nesting selects:

select t1.emp_no, emp_name, emp_dept, tot_reg, tot_ot   from emp t1,

       (select emp_no, sum(work_reg_hrs) tot_reg, sum(work_ot_hrs) tot_ot
          from emp_hrs
         group by emp_no) t2

you probably want to qualify the selection by date in some way...

HTH,                   david