Re: SQL Help

From: news.compaq.com <eugenio.spadafora_at_compaq.com>
Date: 2000/06/07
Message-ID: <8hl21n$db6$1@mailint03.im.hou.compaq.com>#1/1

kasi,
try this one (I'm not completely sure that it's the same due the where condition summary_type='W')

select Code, sum(Actual), sum(Plan)
FROM (select tcc_task_code Code, 0 Actual, sum(amount) Plan

            from planning_summary
            WHERE   summary_type = 'W'
            group by tcc_task_code
            union all
            select tcc_task_code Code, sum(Amount) Actual, 0 Plan
            from  recordings
            group by tcc_task_code)

GROUP BY Code

I hope this helps

Eugenio

kskasi_at_hotmail.com wrote in message <393DFCD5.1CE31FDC_at_hotmail.com>...
>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
>
Received on Wed Jun 07 2000 - 00:00:00 CDT