Re: Outer Join confusion

<bbcrock_at_gmail.com> schrieb im Newsbeitrag news:1130273852.706922.259390_at_g47g2000cwa.googlegroups.com...
> My brain is shot. Can anyone assist me with building this query? I
> have 4 queries right now building 4 result sets, essentially:
>
> select typename from typelookup
>
> select typename, sum(decode(amount,0,planned_amount,amount)) as
> statussummary
> from item
> where status = 1
>
> select typename, sum(decode(amount,0,planned_amount,amount)) as
> statussummary
> from item
> where status = 4
>
> select typename, sum(decode(amount,0,planned_amount,amount)) as
> statussummary
> from item
> where status in (1,4)
>
> I'd like to have a single result set with all the typenames, and the
> summary values for each status and both statuses, but for some reason I
> can't figure out the correct outer join syntax. Can anyone help out?
> Four queries like this are not useful.
>
> The names have been changed to protect the innocent.
>
> Don
>

Hallo,
probably so ?

select t.typename, NVL(status, 'NONE'), statussummary from
scott.typelookup t,
(select typename, status, sum(decode(amount,0,planned_amount,amount)) statussummary
from scott.item
where status in (1,4)
group by typename, status
UNION ALL
select typename, 'Both', sum(decode(amount,0,planned_amount,amount)) statussummary
from scott.item
where status in (1,4)
group by typename) q
where t.typename = q.typename (+)
/

Regards
Dmytro Dekhtyaryuk Received on Wed Oct 26 2005 - 03:12:49 CDT