Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join (Oracle)
Outer Join [message #332099] Mon, 07 July 2008 09:06 Go to next message
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 #332100 is a reply to message #332099] Mon, 07 July 2008 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

My car has a problem.
Tell me how I can make my car go.
Re: Outer Join [message #332101 is a reply to message #332099] Mon, 07 July 2008 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
From one of your previous topics:
Michel Cadot wrote on Mon, 30 June 2008 12:17
1/
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

2/
Always post your Oracle version (4 decimals).

...

Regards
Michel

Re: Outer Join [message #332109 is a reply to message #332101] Mon, 07 July 2008 09:27 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Flashback Transaction Query and date datatypes
Next Topic: Return boolean value for match & non matched records (was:Problem)
Goto Forum:
  


Current Time: Thu Feb 06 14:29:21 CST 2025