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 4 9798 al**********@bt internet.com (Chuck100) wrote in message news:<ee******* *************** ****@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 --
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. al**********@bt internet.com (Chuck100) wrote in message news:<ee******* *************** ****@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 al**********@bt internet.com (Chuck100) wrote in message news:<ee******* *************** ****@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.
To everyone who replied....than ks. The internal query solution
from GQ was the solution I used - worked a treat. db********@yaho o.com (GQ) wrote in message news:<aa******* *************** ***@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.
al**********@bt internet.com (Chuck100) wrote in message news:<ee******* *************** ****@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 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...
MyFirstname,MyLastname,MyAddress
I know this is probebly easy, can someone help me with a code snippit that
does this?
|
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, which when you click it
bookmarks the site (much easier). The favicon is never saved if the site
is bookmarked this way. Does anyone have any ideas how to fix this??
This is the code:
<script language="JavaScript">
|
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
<<<<<<<<<<<<<<CODE>>>>>>>>>>>>>>>>
<html>
|
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
--------------------------------------------------------------------------------
Hello,
I have a very simple problem but cannot seem to figure it out. I have a
very simple php script that sends a test email to myself. When I debug
it in PHP designer, it works with no problems, I get the test email. If
| |
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 attach this script
files and inq files. I cant understand this error. Please suggest me.
You can talk with my yahoo id b_sahoo1@yahoo.com. Now i am online.
Plz....Plz..Plz...
|
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 as fun as coding
it... And yes alarm bells went off when I found myself typing eval(),
and I'm sure this is an 'unusual' use of for: else: . And I know it's
not very robust. As an ex cuse/planation, this is what happens when you
get an idea in...
|
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
********************************************************
For this teeny job, please refer to:
http://feeds.reddit.com/feed/8fu/?o=25
|
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"
onClick="showitem('1234567890'); return false">
Sometimes I just want to copy the item number (i.e. the "value"
attribute), other times I actually want to click the button and see the
item. Right now I can see the item number as a button label, but I
cannot copy its...
|
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: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |