Join [message #331592] |
Fri, 04 July 2008 03:11 |
convey05
Messages: 43 Registered: December 2007 Location: CHENNAI
|
Member |
|
|
Hi,
I have got 2 tables.
Eg. A Table with 2 columns col1,col2 which has same data with different format like Chi/sgn/001 in col1 and chisgn001 in col2
In B table i have a column col1 which can contain data in either of the above mentioned format.
I should create a join and take data from both the tables.
I should also pick up data from A table even if there is no data in B Table.
I gave condition like this
where (a.col1 = b.col1(+)
or a.col2 = b.col1(+))
But it is giving me error.
How to handle this.I cannot use union since both the tables
have different data ,it is showing as 2 records.
Thanks in advance
|
|
|
|
Re: Join [message #331601 is a reply to message #331592] |
Fri, 04 July 2008 03:38 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
It seems like the restriction of Oracle outer join operator. Using ANSI syntax works correctly, as demonstrated in the following testcase: SQL> create table tt1( c1 varchar2(10), c2 varchar2(10) );
Table created.
SQL> create table tt2( c1 varchar2(10) );
Table created.
SQL> insert into tt1( c1, c2 ) values ( 'test1', 'test2' );
1 row created.
SQL> insert into tt1( c1, c2 ) values ( 'test3', 'test4' );
1 row created.
SQL> insert into tt2( c1) values ( 'test1' );
1 row created.
SQL> select * from tt1, tt2 where tt1.c1=tt2.c1(+) or tt1.c2=tt2.c1(+);
select * from tt1, tt2 where tt1.c1=tt2.c1(+) or tt1.c2=tt2.c1(+)
*
ERROR at line 1:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
SQL> select * from tt1 left join tt2 on ( tt1.c1=tt2.c1 or tt1.c2=tt2.c1 );
C1 C2 C1
---------- ---------- ----------
test1 test2 test1
test3 test4
2 rows selected.
Next time, please, provide similar input to precisely show your problem. Oracle version would be useful too (I use 10.1.0.3).
|
|
|
|
|
Re: Join [message #331613 is a reply to message #331611] |
Fri, 04 July 2008 04:11 |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Search How to convert columns into rows.
Hint:-
1) Pivot query
2) MAX(DECODE
Regards,
Rajat
|
|
|
|
Re: Join [message #331621 is a reply to message #331601] |
Fri, 04 July 2008 04:24 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 04 July 2008 10:13 | Why outer join?
|
Does not "I should also pick up data from A table even if there is no data in B Table." state it?
Then there is a question, why join at all.
flyboy wrote on Fri, 04 July 2008 10:38 | Next time, please, provide similar input to precisely show your problem.
| Next time is now. Take a small break and read your posts once again. Do you think that anybody except you is able to decipher from them, what are you trying to solve?
|
|
|
Re: Join [message #331625 is a reply to message #331620] |
Fri, 04 July 2008 04:34 |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
converting rows into columns using Max(Decode
select max(decode(chk,'1',column_1)) column_1,max(decode(chk,'2',column_1)) column_2
from
(
select 1 column_1 ,'1' chk from dual
union
select 2 column_2,'2' chk from dual
)
Regards,
Rajat
|
|
|
|
|
Re: Join [message #331654 is a reply to message #331630] |
Fri, 04 July 2008 06:20 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 04 July 2008 11:42 | Now I'm lost in this topic that goes in multiple directions.
Anyway others are able to follow it.
|
I am afraid that without clear summary from original poster, you are true (as both his posts are referring different issues).
I would guess he needs STRAGG / CONCAT_ALL function to achieve his goal; but as he is very brief, it is impossible to answer accurately.
|
|
|