Home » SQL & PL/SQL » SQL & PL/SQL » Union question (Oracle 9i)
Union question [message #347138] Wed, 10 September 2008 18:48 Go to next message
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 #347147 is a reply to message #347138] Wed, 10 September 2008 19:40 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
This was a decent initial post.

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Part of the guidelines say you should actually post results and you should post the desired results.
Re: Union question [message #347194 is a reply to message #347138] Thu, 11 September 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CASE seems to fit what you want.

Regards
Michel
Re: Union question [message #347460 is a reply to message #347194] Thu, 11 September 2008 13:45 Go to previous messageGo to next message
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 #347465 is a reply to message #347460] Thu, 11 September 2008 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Part of the guidelines say you should actually post results and you should post the desired results.

Regards
Michel
Re: Union question [message #347466 is a reply to message #347465] Thu, 11 September 2008 15:02 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Smile

[Updated on: Thu, 11 September 2008 21:47]

Report message to a moderator

Re: Union question [message #347498 is a reply to message #347138] Thu, 11 September 2008 22:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
satm2008,

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above


[Updated on: Thu, 11 September 2008 22:01] by Moderator

Report message to a moderator

Re: Union question [message #347524 is a reply to message #347498] Fri, 12 September 2008 00:26 Go to previous messageGo to next message
satm2008
Messages: 10
Registered: September 2008
Location: Toronto, Canada
Junior Member
@anacdent

I am afraid I did not understand what is missing in my post, probably the code in formatted. Is that so?

Could you please clarify?

[Updated on: Fri, 12 September 2008 00:27]

Report message to a moderator

Re: Union question [message #347526 is a reply to message #347138] Fri, 12 September 2008 00:34 Go to previous messageGo to next message
satm2008
Messages: 10
Registered: September 2008
Location: Toronto, Canada
Junior Member
Here it is, formatted Smile

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 Go to previous messageGo to next message
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 Go to previous message
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 Smile

[Updated on: Fri, 12 September 2008 13:38]

Report message to a moderator

Previous Topic: Update Statement
Next Topic: Excel Column Name too long
Goto Forum:
  


Current Time: Sat Dec 10 01:19:24 CST 2016

Total time taken to generate the page: 0.06036 seconds