Help writing an SQL Script

[Chuck100]

I'm having problems with the output of the following script (I've
simplified it):-

select a.section,a.use r,count(b.numbe r),null
from table a, table b
where.........
group by a.section,a.use r
union
select a.section,a.use r,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

[Mark D Powell]

Re: Help writing an SQL Script

alan.terry74@bt internet.com (Chuck100) wrote in message news:<eecdee57. 0407050707.1458 aae2@posting.go ogle.com>...

I'm having problems with the output of the following script (I've
simplified it):-
>
select a.section,a.use r,count(b.numbe r),null
from table a, table b
where.........
group by a.section,a.use r
union
select a.section,a.use r,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 --

[GQ]

Re: Help writing an SQL Script

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.use r,count(b.numbe r) Col_A,count(c.n umber) 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.use r;

2. Using internal queries
Select sub_A.section,s ub_A.user,sum(s ub_A.Col_A) ColA,sum(sub_B. Col_B) ColB
from (select a.section,a.use r,count(b.numbe r) Col_A
from table a, table b
where.........
and a.key=b.key(+)
group by a.section,a.use r) sub_A,
(select a.section,a.use r,count(c.numbe r) Col_B
from table a, table c
where.........
and a.key=c.key(+)
group by a.section,a.use r) sub_B,
where sub_A.section=s ub_B.section
and sub_A.user=sub_ B.user
group by sub_A.section,s ub_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@bt internet.com (Chuck100) wrote in message news:<eecdee57. 0407050707.1458 aae2@posting.go ogle.com>...

I'm having problems with the output of the following script (I've
simplified it):-
>
select a.section,a.use r,count(b.numbe r),null
from table a, table b
where.........
group by a.section,a.use r
union
select a.section,a.use r,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

[Ed prochak]

Re: Help writing an SQL Script

alan.terry74@bt internet.com (Chuck100) wrote in message news:<eecdee57. 0407050707.1458 aae2@posting.go ogle.com>...

I'm having problems with the output of the following script (I've
simplified it):-
>
select a.section,a.use r,count(b.numbe r),null
from table a, table b
where.........
group by a.section,a.use r
union
select a.section,a.use r,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

Then you want a join query instead of a union.

[Chuck100]

Re: Help writing an SQL Script

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


dbaguy_ott@yaho o.com (GQ) wrote in message news:<aad8b5cb. 0407061302.58e8 ccf@posting.goo gle.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.use r,count(b.numbe r) Col_A,count(c.n umber) 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.use r;
>
2. Using internal queries
Select sub_A.section,s ub_A.user,sum(s ub_A.Col_A) ColA,sum(sub_B. Col_B) ColB
from (select a.section,a.use r,count(b.numbe r) Col_A
from table a, table b
where.........
and a.key=b.key(+)
group by a.section,a.use r) sub_A,
(select a.section,a.use r,count(c.numbe r) Col_B
from table a, table c
where.........
and a.key=c.key(+)
group by a.section,a.use r) sub_B,
where sub_A.section=s ub_B.section
and sub_A.user=sub_ B.user
group by sub_A.section,s ub_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@bt internet.com (Chuck100) wrote in message news:<eecdee57. 0407050707.1458 aae2@posting.go ogle.com>...

I'm having problems with the output of the following script (I've
simplified it):-

select a.section,a.use r,count(b.numbe r),null
from table a, table b
where.........
group by a.section,a.use r
union
select a.section,a.use r,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