Home » Developer & Programmer » Reports & Discoverer » Union Query
Union Query [message #216859] |
Tue, 30 January 2007 13:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
chmlaeeque
Messages: 59 Registered: September 2006
|
Member |
|
|
Hi Every body,
i m using union query to 2 tables t1 and t2 having same cols. code,detail.
but now i have add another col, narr on both tables but now when i view the report it seems any problem that the single item of narr be viewed diff. times e.g.
select code,detail,narr from ( select code,detail,narr from t1
union
select code,detail,narr from t2 )
group by code,detail, narr
OUTPUT IS
Code Detail Narr
1 John Clerk
1 John
1 John Clerk
2 Staifi MD
3 James Dir.
3 James
somthing like this, ANY SOLUTION
M. Laeeque
|
|
|
|
Re: Union Query [message #217107 is a reply to message #216888] |
Wed, 31 January 2007 12:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
chmlaeeque
Messages: 59 Registered: September 2006
|
Member |
|
|
No Problem, what u understand.
just c i have 2 tables having same columns with same data type but different values. 2 columns i.e. code, detail are rather same on both tables and when i use union query to view the Distinct Codes Used by them. it views correct e.g
IN T1 :-
code detail
001 Ahmad
002 Jahan
003 Shabbir
IN T2 :-
Code Detail
001 Ahmad
004 John
when i use union query :-
select code,detail from
( select code,detail from t1
union
select code,detail from t2 )
group by code,detail
It Views Correct i.e.
Code Detail
001 Ahmad
002 Jahan
003 Shabbir
004 John
but now i have add a column name NARR to the union query it consist in both tables but the value will mostly diff. in both tables as u can say the name of party.
The query i use is :-
select code,detail, narr from
( select code,detail, narr from t1
union
select code,detail, narr from t2 )
group by code,detail, narr
but in this situation my view gets wrong i.e. if the code is used in both tables with diff. NARR the number of records increases in Report. I.e ( i have attached the output ), i know that it will group as the narr change but is there any other way the the narr changes but the record view in single line, as i select from which table it will select.
-
Attachment: untitled.JPG
(Size: 112.95KB, Downloaded 731 times)
[Updated on: Wed, 31 January 2007 12:40] Report message to a moderator
|
|
|
Re: Union Query [message #217127 is a reply to message #217107] |
Wed, 31 January 2007 15:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
It seems that we don't understand each other ... Let me try now; please, follow this example and say where I went wrong. I couldn't follow your pseudoexample (which I also used here) and compare it to the attached image, because I wasn't able to see what is what.
First, create our test case:SQL> CREATE TABLE t1 (code VARCHAR2(3), detail VARCHAR2(20));
Table created.
SQL> CREATE TABLE t2 (code VARCHAR2(3), detail VARCHAR2(20));
Table created.
SQL> INSERT ALL
2 INTO t1 VALUES ('001', 'Ahmad')
3 INTO t1 VALUES ('002', 'Jahan')
4 INTO t1 VALUES ('003', 'Shabbir')
5 INTO t2 VALUES ('001', 'Ahmad')
6 INTO t2 VALUES ('004', 'John')
7 SELECT * FROM dual;
5 rows created.
SQL> SELECT code, detail FROM t1
2 UNION
3 SELECT code, detail FROM t2
4 ORDER BY 1, 2;
COD DETAIL
--- --------------------
001 Ahmad
002 Jahan
003 Shabbir
004 John Now add another column and update our tables:SQL> ALTER TABLE t1 ADD narr NUMBER(1);
Table altered.
SQL>
SQL> ALTER TABLE t2 ADD narr NUMBER(1);
Table altered.
SQL> UPDATE t1 SET
2 narr = CASE
3 WHEN code = '001' THEN 1
4 WHEN code = '002' THEN 2
5 WHEN code = '003' THEN 3
6 END;
3 rows updated.
SQL> UPDATE t2 SET
2 narr = CASE
3 WHEN code = '001' THEN 4
4 WHEN code = '004' THEN 5
5 END;
2 rows updated.
This is what we have now:SQL> SELECT code, detail, narr FROM t1
2 UNION
3 SELECT code, detail, narr FROM t2
4 ORDER BY 1, 2, 3;
COD DETAIL NARR
--- -------------------- ----------
001 Ahmad 1
001 Ahmad 4
002 Jahan 2
003 Shabbir 3
004 John 5
SQL>
So, what is wrong with that output? What would you like to have instead? Use this example and post the expected result. Please, use [code] tags to improve reading.
|
|
|
|
|
Goto Forum:
Current Time: Tue Aug 27 11:09:17 CDT 2024
|