Home » SQL & PL/SQL » SQL & PL/SQL » Join (Oracle)
Join [message #331592] Fri, 04 July 2008 03:11 Go to next message
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 #331595 is a reply to message #331592] Fri, 04 July 2008 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why outer join?

Regards
Michel
Re: Join [message #331601 is a reply to message #331592] Fri, 04 July 2008 03:38 Go to previous messageGo to next message
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 #331606 is a reply to message #331601] Fri, 04 July 2008 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The question still remains "is the outer join needed in the original requirement?".

Regards
Michel
Re: Join [message #331611 is a reply to message #331601] Fri, 04 July 2008 04:03 Go to previous messageGo to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
I need data in a single row.

Re: Join [message #331613 is a reply to message #331611] Fri, 04 July 2008 04:11 Go to previous messageGo to next message
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 #331620 is a reply to message #331613] Fri, 04 July 2008 04:23 Go to previous messageGo to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
Can u pls explain it wih example
Re: Join [message #331621 is a reply to message #331601] Fri, 04 July 2008 04:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #331626 is a reply to message #331625] Fri, 04 July 2008 04:36 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Sorry Columns into rows

Regards,
Rajat
Re: Join [message #331630 is a reply to message #331621] Fri, 04 July 2008 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
flyboy wrote on Fri, 04 July 2008 11:24
Does not "I should also pick up data from A table even if there is no data in B Table." state it?

Yes, miss it. Thanks but why is this not OP that answers this? Wink
Now I'm lost in this topic that goes in multiple directions.
Anyway others are able to follow it.

Regards
Michel

Re: Join [message #331654 is a reply to message #331630] Fri, 04 July 2008 06:20 Go to previous message
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.
Previous Topic: User_jobs
Next Topic: Search through REGEXP_INSTR function
Goto Forum:
  


Current Time: Thu Dec 05 16:12:17 CST 2024