Home » SQL & PL/SQL » SQL & PL/SQL » Union question (Oracle 9i)
Union question [message #347138] |
Wed, 10 September 2008 18:48  |
rachana
Messages: 27 Registered: August 2003
|
Junior Member |
|
|
Hi,
I want to merge two columns from two different tables and get a count. I am getting inflated counts.
I have two tables as-
CREATE TABLE Response (
respId NUMBER(4) NOT NULL,
reqId NUMBER(4),
Resolution VARCHAR2(250),
Title VARCHAR2(250),
DocID VARCHAR2(50)
LETNO NUMBER(4),
TYPE VARCHAR(50))
CREATE TABLE ResponseChild (
respId NUMBER(4) NOT NULL,
reqId NUMBER(4),
Childtext VARCHAR2(250),
ChildType NUMBER(5)
ChildCode VARCHAR2 (50))
INSERT INTO TABLE Response (
respId, reqId , Resolution, Title , DocID , LETNO, TYPE
values
'19985', '20000', 'My Resolution answer(Verbal)', 'My Document1', 'FV001', 3344, NULL )
INSERT INTO TABLE Response (
respId, reqId , Resolution, Title , DocID, LETNO, TYPE
values
'19985', '20000', NULL, 'My Document2', 'TD33', 4567, NULL)
INSERT INTO TABLE Responsechild (
respId, reqId , childtext, childtype, childcode
values
'19985', '20000', 'My Resolution work(Written)', 2, NULL)
INSERT INTO TABLE Responsechild (
respId, reqId , childtext, childtype, childcode
values
'19985', '20000', 'My Document1', 3, TD33)
I want to merge the columns from the two tables as follows-
-if responsechild.childtype = 2, then merge the Response.Resolution and the responsechild.childtext
- if if responsechild.childtype = 3, then merge the Response.title and the responsechild.childtext and also merge the response.docId and responsechild.childcode
I have tried the following code, and am getting inflated numbers. What am I doing wrong?
SELECT COUNT(dt_Response_Resolution.t_Resolution),
COUNT(dt_Response_Document.t_Title),
COUNT(dt_Response_Document.t_DocId),
dt_Response_Resolution.t_Resolution,
dt_Response_Document.t_Title,
dt_Response_Document.t_DocId
FROM Response z,
(SELECT dt_rc.Resolution AS t_Resolution,
b.rEspId,
b.reqId
FROM Response b,
(SELECT a.reqId,
a.rEspId,
a.Resolution
FROM Response a
UNION
SELECT b.reqId,
b.rEspId,
b.ChildText
FROM ResponseChild b
WHERE b.ChildType = '2') dt_rc
WHERE b.reqId = dt_rc.reqId (+)
AND b.rEspId = dt_rc.rEspId (+) ) dt_Response_Resolution,
(SELECT dt_Doc.Title AS t_Title,
dt_Doc.DocId AS t_DocId,
c.rEspId,
c.reqId
FROM Response c,
(SELECT a1.reqId,
a1.rEspId,
a1.Title,
a1.DocId
FROM Response a1
UNION
SELECT b.reqId,
b.rEspId,
b.ChildText,
b.ChildCode
FROM ResponseChild b
WHERE b.ChildType = '3') dt_Doc
WHERE c.reqId = dt_Doc.reqId (+)
AND c.rEspId = dt_Doc.rEspId (+) ) dt_Response_Document
WHERE ((z.reqId.reqId = dt_Response_Resolution.reqId)
AND dt_Response_Document.reqId = z.reqId)
GROUP BY dt_Response_Resolution.t_Resolution,
dt_Response_Document.t_Title,
dt_Response_Document.t_DocId
Thanks much for your help.
|
|
|
|
|
Re: Union question [message #347460 is a reply to message #347194] |
Thu, 11 September 2008 13:45   |
rachana
Messages: 27 Registered: August 2003
|
Junior Member |
|
|
Results Returned by my query:-
For count of Resoltion from the two tables, it returns 4. It should return 2 ('My Resolution answer(Verbal), and, 'My Resolution work(Written))
How do I use a case statement between two tables. I need to union the two tables to merge/union the columns as described in my post below.
|
|
|
|
Re: Union question [message #347466 is a reply to message #347465] |
Thu, 11 September 2008 15:02   |
rachana
Messages: 27 Registered: August 2003
|
Junior Member |
|
|
Below is the reults I got:-
count(T_Resolution) count(T_Title) Count(T_DocID) T_Resolution T_Title T_DocID
6 6 6 My Resolution Answer (Verbal) My Document 1 FV001
6 6 6 My Resolution Answer (Verbal) My Document 2 TD33
6 6 6 My Reolution Work (Written) My Document 1 FV001
6 6 6 My Reolution Work (Written) My Document 2 TD33
Even if I exclude the T_Resolution, T_Title, and T_DocID from the select, I get this-
count(T_Resolution) count(T_Title) Count(T_DocID)
6 6 6
This is the correct result I should get:-
count(T_Resolution) count(T_Title) Count(T_DocID)
2 2 2
|
|
|
Re: Union question [message #347496 is a reply to message #347466] |
Thu, 11 September 2008 21:41   |
satm2008
Messages: 10 Registered: September 2008 Location: Toronto, Canada
|
Junior Member |
|
|
The problem, at a glance, I could see is that your outer join query. Since it is showing the rows match and unmatched, it is returning the rows as cartasian product and giving a redundant count.
I am not sure why you wrote such a long query but if I understand it right, it can be done in a simple UNION of two SELECT which one selects rows based on the query, responsechild.childtype = 2 and other selects rows where responsechild.childtype = 3. Let me put it in a simpler way (no outer join as it should return a count of only "matching").
The query would look like:
SELECT t_reqid, t_respid, docid, count(*)
FROM (
SELECT response.reqid as t_reqid, response.respid as t_respid, docid, response.title, responsechild.childtext, response.docid, responsechild.childcode
FROM responsechild, response -- make response as driver
WHERE responsechild.ChildType = '3'
AND responsechild.reqId = response.reqId -- check child against parent, not other way around
AND response.respid = response.respid
UNION
SELECT response.reqid, response.respid, docid, response.resolution, responsechild.childtext, ' ' as dummy_docid, ' ' as dummy_childcode
FROM responsechild, response -- make response as driver
WHERE responsechild.ChildType = '2'
AND responsechild.reqId = response.reqId -- check child against parent, not other way around
AND response.respid = response.respid )
GROUP BY t_reqid, t_respid, docid;
Since the UNONized SELECT should have similar count/type of columns being merged, so I used dummy blank columns for resolution and childtext.
In oracle, for better performance, keep the parent/driver in right most and check the leading/children against the parent/driver table. And consider checking the main logic bottom-up method. It would give you better performance.
Check the above query and post the outcome.
Good luck
[Updated on: Thu, 11 September 2008 21:47] Report message to a moderator
|
|
|
|
|
Re: Union question [message #347526 is a reply to message #347138] |
Fri, 12 September 2008 00:34   |
satm2008
Messages: 10 Registered: September 2008 Location: Toronto, Canada
|
Junior Member |
|
|
Here it is, formatted 
SELECT t_reqid, t_respid, docid, count(*)
FROM (
SELECT response.reqid as t_reqid, response.respid as t_respid, docid, response.title, responsechild.childtext, response.docid, responsechild.childcode
FROM responsechild, response -- make response as driver
WHERE responsechild.ChildType = '3'
AND responsechild.reqId = response.reqId -- check child against parent, not other way around
AND response.respid = response.respid
UNION
SELECT response.reqid, response.respid, docid, response.resolution, responsechild.childtext, ' ' as dummy_docid, ' ' as dummy_childcode
FROM responsechild, response -- make response as driver
WHERE responsechild.ChildType = '2'
AND responsechild.reqId = response.reqId -- check child against parent, not other way around
AND response.respid = response.respid )
GROUP BY t_reqid, t_respid, docid
;
[Updated on: Fri, 12 September 2008 00:35] Report message to a moderator
|
|
|
Re: Union question [message #347674 is a reply to message #347526] |
Fri, 12 September 2008 11:50   |
rachana
Messages: 27 Registered: August 2003
|
Junior Member |
|
|
Hi Sam2008,
Thanks a lot for your response. The Counts seem correct, however, one issue still remains.
I need to merge/union the columns - Resolution and Childtext where childtype =2.
I also need to merge DocID and ChildCode and Title with childtext where childtype =3.
Thats why I had the union between response and responsechild. But since there is no condition saying
where response.reqid = responsechild.reqid AND response.respid = responsechild.respid
, I am getting ALL the rows. How do I insert this condition in the union itself?
(I cannot use insert table, or PL/SQL as this code goes within a reporting application that can only do select statements)
[Updated on: Fri, 12 September 2008 11:54] Report message to a moderator
|
|
|
Re: Union question [message #347682 is a reply to message #347674] |
Fri, 12 September 2008 13:37  |
satm2008
Messages: 10 Registered: September 2008 Location: Toronto, Canada
|
Junior Member |
|
|
As you see it in the criteria given for each SELECT of UNIONized view, the first one selects the rows of childtype 2 and the other one of childtype 3.
The data filter is done already in the unionized view itself.
The main query is just to summarize the results with their counts.
If you read the query thoroughly you understand it, I believe.
I gave you an example of the columns merged as you noted, but if you want more, sure you can do so adding/modifying it.
And the respid and reqid are needed for both the inner selects as the parent and child tables have the key relation and SHOULD be matched to pickup the parent-child only rows.
Hope it is clear now!
Good luck
[Updated on: Fri, 12 September 2008 13:38] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Tue Feb 11 16:53:29 CST 2025
|