Outer Join [message #332099] |
Mon, 07 July 2008 09:06  |
convey05
Messages: 43 Registered: December 2007 Location: CHENNAI
|
Member |
|
|
Hi,
I have got 2 Tables like the one displayed below
Table - Test1
no name
shi/chi/oo1 sss
chi/sin/002 bbb
ind/usa/003 ccc
Table -- Test2
no ed_no age sal
shi/chi/oo1 shichi001 20 2000
chi/sin/002 chisin002 20 4000
I have to get data from both the tables in a single row so i am
using join condition.I should always get data from Table Test1
even if data is not there in Table Test2.So i am using Outer join
and the ed_No can be with '/' and without '/' both should be matched
so i gave query like this
select a.no,a.name,b.age,b.sal
from(
select no,name
from test1
where no='chi/sin/002') a,
select age,sal
from test2
where no='chi/sin/002 '
or ed_no='chisin002') b
where (a.no =b.no(+) or b.ed_no = replace(a.no,'/',''))
and a.no='chi/sin/002';
This query is giving me error.
can any one tell me how to do it
Thanks in advance
[Added code tags]
[Updated on: Mon, 07 July 2008 09:13] by Moderator Report message to a moderator
|
|
|
|
|
Re: Outer Join [message #332109 is a reply to message #332101] |
Mon, 07 July 2008 09:27   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Working back from your query, I think what you're trying to do is outer join A to B on one of two different columns.
This won't work, and would give you an ORA-1719. I've no real idea if this is the problem, as you didn't bother to tell us the error you got.
Assuming I'm right, try this (I've played with the test data a bit to show up the effective conditional outer join):drop table test_0039;
drop table test_0040;
create table test_0039 (no varchar2(30), name varchar2(30));
insert into test_0039 values ('shi/chi/001', 'sss');
insert into test_0039 values ('chi/sin/002', 'bbb');
insert into test_0039 values ('ind/usa/003', 'ccc');
create table test_0040 ( no varchar2(30), ed_no varchar2(30), age number, sal number);
insert into test_0040 values ('shi/chi/oo1', 'shichi001', 20, 2000);
insert into test_0040 values ('chi/sin/002' , 'chisinoo2', 20, 4000);
select a.no
,a.name
,nvl(b.no,c.no)
,nvl(b.ed_no,c.ed_no)
from test_0039 a
,test_0040 b
,test_0040 c
where a.no = b.no(+)
and replace(a.no,'/','') = c.ed_no(+);
NO NAME NVL(B.NO,C.NO) NVL(B.ED_NO,C.ED_NO)
------------------------------ ------------------------------ ------------------------------ ------------------------------
shi/chi/001 sss shi/chi/oo1 shichi001
chi/sin/002 bbb chi/sin/002 chisinoo2
ind/usa/003 ccc null null
|
|
|
Re: Outer Join [message #332111 is a reply to message #332109] |
Mon, 07 July 2008 09:32   |
convey05
Messages: 43 Registered: December 2007 Location: CHENNAI
|
Member |
|
|
Hi,
I have two types of data that is with '/' and without '/'
in the no column and ed_no column.
So i am using OR Clause inside the where clause.
Can u tell me how to use it.
|
|
|
Re: Outer Join [message #332120 is a reply to message #332099] |
Mon, 07 July 2008 09:58  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
YOu said you were getting an error...
Where is the error message and why have you not posted it?
Kevin
|
|
|