Re: Help writing an SQL Script
Date: 6 Jul 2004 14:02:43 -0700
Message-ID: <aad8b5cb.0407061302.58e8ccf_at_posting.google.com>
There are several ways to rewrite this query ... here are two
1. Using outer joins, when b or c not present, the count of null wont increase
Select a.section,a.user,count(b.number) Col_A,count(c.number) Col_B
from table a, table b, table c
2. Using internal queries
I think that you wont need the sum or group by in the second statement.
I've specified outer joins in the internal queries, because I don't know
if your return set would otherwise be one for one ?
GQ.
alan.terry74_at_btinternet.com (Chuck100) wrote in message news:<eecdee57.0407050707.1458aae2_at_posting.google.com>...
where .......
and a.key=b.key(+)
and a.key=c.key(+)
group by a.section,a.user;
Select sub_A.section,sub_A.user,sum(sub_A.Col_A) ColA,sum(sub_B.Col_B) ColB
from (select a.section,a.user,count(b.number) Col_A
from table a, table b
where.........
and a.key=b.key(+)
group by a.section,a.user) sub_A,
(select a.section,a.user,count(c.number) Col_B
from table a, table c
where.........
and a.key=c.key(+)
group by a.section,a.user) sub_B,
where sub_A.section=sub_B.section
and sub_A.user=sub_B.user
group by sub_A.section,sub_A.user;
> I'm having problems with the output of the following script (I've
> simplified it):-
>
> select a.section,a.user,count(b.number),null
> from table a, table b
> where.........
> group by a.section,a.user
> union
> select a.section,a.user,null,count(c.number)
> from table a, table c
> where .......
> group by a.section, a.user
>
> The output looks like this:-
>
> Section User col A col b
> ------- ---- ----- -----
>
> section 1 user A 22 0
> 0 12
>
> section 2 user d 10 0
> 0 9
>
> I want the output to look like this:-
>
> Section User col A col b
> ------- ---- ----- -----
>
> section 1 user A 22 12
>
> section 2 user d 10 9
>
> i.e I don't want a second line reflecting the statement after the
> union command. I want the entry to appear on the one line. I''m using
> Report Oracle Reports Builder to do the script
>
> Thanks
Received on Tue Jul 06 2004 - 23:02:43 CEST