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

alan.terry74 (AT) btinternet (DOT) com (Chuck100) wrote in message news:<eecdee57.0407050707.1458aae2 (AT) posting (DOT) google.com>...
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 --

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 (DOT) com (Chuck100) wrote in message news:<eecdee57.0407050707.1458aae2 (AT) posting (DOT) google.com>...

alan.terry74 (AT) btinternet (DOT) com (Chuck100) wrote in message news:<eecdee57.0407050707.1458aae2 (AT) posting (DOT) google.com>...

Then you want a join query instead of a union.

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


dbaguy_ott (AT) yahoo (DOT) com (GQ) wrote in message news:<aad8b5cb.0407061302.58e8ccf (AT) posting (DOT) google.com>...