Re: Help writing an SQL Script

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 6 Jul 2004 13:34:00 -0700
Message-ID: <2687bb95.0407061234.6529f5f8_at_posting.google.com>

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

Ok, if there is only one Col_A with a non-zero value for each unique session, user combination and also only one non-zero Col_B value then the following query should work

select a.session, a.user, a.Col_A, b.Col_B from (select a1.session, a1.user, a1.col_a

      from table_a a1
      where a1.col_a != 0) a
     ,(select b1.session, b1.user, b1.col_b
       from table_a b1
       where b1.col_b != 0) b

where a.session = b.session
and a.user = b.user;

Barring a typo I think the above will work. You should be able to figure out how to get the query into the tool.

HTH -- Mark D Powell -- Received on Tue Jul 06 2004 - 22:34:00 CEST