Re: Help writing an SQL Script

To everyone who replied....thanks. The internal query solution from GQ was the solution I used - worked a treat.

dbaguy_ott_at_yahoo.com (GQ) wrote in message news:<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
> where .......
> and a.key=b.key(+)
> and a.key=c.key(+)
> group by a.section,a.user;
>
> 2. Using internal queries
> 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 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>...
> > 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 Wed Jul 07 2004 - 18:37:01 CEST