OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » Union Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Union Query [message #216859]

Tue, 30 January 2007 13:44

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

Report message to a moderator

Re: Union Query [message #216888 is a reply to message #216859]

Tue, 30 January 2007 15:34

Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm sorry, but I don't understand you. OK, you have two tables. Both of them had same columns, and now you added another column to both tables. This part is OK.

Now you have that query. If I'm not wrong, you'd get the same result without inline view, but never mind that.

Records you showed us are ... what? Result of the query you posted? What's wrong with it? What would you like to get as a result?

Could you, please, explain the problem once again? Providing sample data set along with desired result would be just fine.

Report message to a moderator

Re: Union Query [message #217107 is a reply to message #216888]

Wed, 31 January 2007 12:37

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 688 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

Littlefoot
Messages: 21813
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.

Report message to a moderator