Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL Help

Re: SQL Help

From: <michael_bialik_at_my-deja.com>
Date: 2000/06/07
Message-ID: <8hm975$g0v$1@nnrp1.deja.com>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US