Re: SQL Help

Hi.

 Try following

 SELECT tcc_task_code, SUM(actual), SUM(plan)  FROM
  ( select tcc_task_code, 0, sum(amount) Plan     from planning_summary
    WHERE summary_type = 'W'
    group by tcc_task_code, 0
    UNION ALL
    select tcc_task_code, sum(Amount) Actual, 0     from recordings
   group by tcc_task_code, 0 )

 I couldn't check it on Oracle now, but I think it should work.

 HTH. Michael

In article <393DFCF6.580BF049_at_hotmail.com>,   kskasi_at_hotmail.com wrote:
> Hello everyone
>
> I have the following SQL
>
> select Tot.tcc_task_code, rec.Actual, pls.Plan
> FROM (select tcc_task_code, sum(amount) Plan
> from planning_summary
> WHERE summary_type = 'W'
> group by tcc_task_code) pls,
> (select tcc_task_code, sum(Amount) Actual
> from recordings
> group by tcc_task_code) rec,
> (select tcc_task_code from planning_summary
> union
> select tcc_task_code from recordings) Tot
> WHERE tot.tcc_task_code = rec.tcc_task_code(+)
> and tot.tcc_task_code = pls.tcc_task_code(+)
>
> So I basicaly use 3 inline views to get the following output
>
> tcc_task_code Actual Plan
> 2000 $10
> 2001 $20
> 2003 $40 $50
>
> I feel that the last union statement is slowing down the SQL. The
 reason
> for I have
> that is, few task codes might exist in planning_summary table and not
 in
> recordings
> and also the vise versa. So even if we use outer join, we might miss
> some tcc_task_codes
> without using unions. Can this be done anyother way effeciently.
>
> thanks...kasi
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jun 07 2000 - 00:00:00 CDT