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 4 9774 al**********@btinternet.com (Chuck100) wrote in message news:<ee**************************@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 --
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. al**********@btinternet.com (Chuck100) wrote in message news:<ee**************************@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 al**********@btinternet.com (Chuck100) wrote in message news:<ee**************************@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
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. db********@yahoo.com (GQ) wrote in message news:<aa*************************@posting.google.c om>... 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.
al**********@btinternet.com (Chuck100) wrote in message news:<ee**************************@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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: eboogyman |
last post by:
I have an HTML form with Firstname, lastname, address
I want to save the firstname, lastname and address appended to a file in say
results.txt, in the below format 1 entry per line...
...
|
by: Phil Powell |
last post by:
<?php
class FileRemoval {
var $fileNameArray, $isRemoved, $errorMsg = '';
function FileRemoval() {
$this->fileNameArray = array();
$this->isRemoved = 0;
}
|
by: Johnny Knoxville |
last post by:
I've added a favicon to my site (http://lazyape.filetap.com/) which works
fine if you add the site to favourites the normal way, but I have some
JavaScript code on a couple of pages with a link,...
|
by: Craig Keightley |
last post by:
Please help, i have attached my page which worksin IE but i cannnot get the
drop down menu to fucntion in firefox. Any one have any ideas why?
Many Thanks
Craig
...
|
by: baustin75 |
last post by:
Posted: Mon Oct 03, 2005 1:41 pm Post subject: cannot mail() in ie
only when debugging in php designer 2005
--------------------------------------------------------------------------------
...
|
by: Rahul |
last post by:
Hi Everybody
I have some problem in my script. please help me. This is script file.
I have one *.inq file. I want run this script in XML files. But this
script errors shows . If u want i am...
|
by: Matthew Warren |
last post by:
I have the following piece of code, taken from a bigger module, that
even as I was writing I _knew_ there were better ways of doing it, using
a parser or somesuch at least, but learning how wasn't...
|
by: gunimpi |
last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431
********************************************************
VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help
wanted...
|
by: MKR |
last post by:
I need some help writing a Greasmonkey user script. An application that
I use generates HTML pages with tags like this:
<input type="button" name="9999" value="1234567890"...
|
by: vijaymohan |
last post by:
Hi ..I am very new to perl..can some one help me with this script pls..
I am Querying database and writing data to excel..
Here is my script:
#!/usr/bin/perl -w
use strict;
use DBI;
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |