Home » Developer & Programmer » Reports & Discoverer » How to merg these 2 quries
How to merg these 2 quries [message #203196] Tue, 14 November 2006 04:45 Go to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
I have these 2 queries I am creating a summaries report Please help me how to merg these 2 queries?

Q#1 : select f.ppno, f.subpp, sum(s.quantity) DQTY
from sub_fabric_delivered s, fabric_delivered f
where s.fdno = f.fdno
group by f.ppno, f.subpp

Q#2: select p.ppno, p.subpp, sum(sp.req_kgs) RQTY
from sub_program sp, program p
where sp.jobno=p.jobno
group by p.ppno, p.subpp

******************************
Please note I created a query but it is not showing proper result.
Query is :
SELECT f.ppno, f.subpp, p.item, sum(sp.req_kgs) RKGS,SUM (s.quantity) qty, s.fdno
FROM sub_fabric_delivered s, fabric_delivered f, program p,
sub_program sp
WHERE s.fdno = f.fdno
AND sp.jobno = p.jobno
AND f.ppno = p.ppno
and s.color = sp.color
GROUP BY s.fdno,sp.req_kgs, f.ppno, f.subpp, p.item
ORDER BY f.ppno,f.subpp; 
Re: How to merg these 2 quries [message #203200 is a reply to message #203196] Tue, 14 November 2006 05:26 Go to previous messageGo to next message
Ferrarist
Messages: 29
Registered: March 2006
Location: Netherlands - Den Haag
Junior Member
How about:

Select sub1.ppno, sub1.subpp, sub1.DQTY, sub2.RQTY
from (select f.ppno ppno, f.subpp subpp, sum(s.quantity) DQTY
from sub_fabric_delivered s, fabric_delivered f
where s.fdno = f.fdno
group by f.ppno, f.subpp) sub1,
(select p.ppno ppno, p.subpp subpp, sum(sp.req_kgs) RQTY
from sub_program sp, program p
where sp.jobno = p.jobno
group by p.ppno, p.subpp) sub2
where sub1.ppno = sub2.ppno(+)
and sub1.subpp = sub2.subpp(+)
union
Select p.ppno, p.subpp, null DQTY, sum(p.req_kgs) RQTY
from sub_program sp, program p
where sp.jobno = p.jobno
and not exists (select f.ppno ppno, f.subpp subpp
from sub_fabric_delivered s, fabric_delivered f
where s.fdno = f.fdno
and f.ppno = p.ppno
and f.subpp = p.subpp)
group by p.ppno, p.subpp

The select before the union selects all the results from your Q1 and outer joins them with the results from Q2. The result is all the rows from Q1 and if there are corresponding results in Q2, they're joined.
The select after the union selects all the results from Q2 that have not been joined in the select above the union.
Re: How to merg these 2 quries [message #203235 is a reply to message #203196] Tue, 14 November 2006 07:15 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member

hi,

Please send me the dummy data and table stucture. I will try here.

Bye
Ashu
Previous Topic: report size
Next Topic: Problems to get a Discoverer sheet
Goto Forum:
  


Current Time: Sun Dec 04 18:36:46 CST 2016

Total time taken to generate the page: 0.18731 seconds