Home » SQL & PL/SQL » SQL & PL/SQL » WITH Clause with Full Outer Join (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod)
WITH Clause with Full Outer Join [message #348197] Tue, 16 September 2008 00:09 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

I am facing problem joining more than 2 tables involving WITH clause and FULL OUTER JOIN..

create table test_outer
(col1 varchar2(10),
 col2 varchar2(10));
 
insert into test_outer
values('DIT','1');
insert into test_outer
values('DIT','2');
insert into test_outer
values('DIT','3');
insert into test_outer
values('DIT','4');
insert into test_outer
values('SIT','1');
insert into test_outer
values('SIT','2');
insert into test_outer
values('SIT','3');
insert into test_outer
values('SIT','4');
insert into test_outer
values('UAT','1');
insert into test_outer
values('UAT','2');
insert into test_outer
values('UAT','3');
insert into test_outer
values('UAT','4');
 
SQL> with datav as
  2  (select * from test_outer)
  3  select nvl(dit.col2, sit.col2) val1
  4  FROM
  5  (select * from datav
  6  where col1 = 'DIT'
  7  ) DIT
  8  FULL OUTER JOIN
  9  (select * from datav
 10  where col1 = 'SIT'
 11  ) SIT
 12  ON DIT.col2 = SIT.col2;

VAL1
----------
1
2
3
4

SQL> with datav as
  2  (select * from test_outer)
  3  select nvl(dit.col2, sit.col2) val1
  4  FROM
  5  (select * from datav
  6  where col1 = 'DIT'
  7  ) DIT
  8  FULL OUTER JOIN
  9  (select * from datav
 10  where col1 = 'SIT'
 11  ) SIT
 12  ON DIT.col2 = SIT.col2
 13  FULL OUTER JOIN
 14  (select * from datav
 15  where col1 = 'UAT'
 16  ) UAT
 17  ON DIT.col2 = UAT.col2;
(select * from test_outer)
*
ERROR at line 2:
ORA-00942: table or view does not exist



Any reason why is this error or how it can be avoided?
Re: WITH Clause with Full Outer Join [message #348204 is a reply to message #348197] Tue, 16 September 2008 00:36 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
It's working fine on My system.

Which version of oracle you are using.

I am using 9.2.0.6.0.

Regards,
Rajat
Re: WITH Clause with Full Outer Join [message #348205 is a reply to message #348197] Tue, 16 September 2008 00:41 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Quote:
It's working fine on My system.

Which version of oracle you are using.

I am using 9.2.0.6.0.

Regards,
Rajat


Shocked ... I am using 10g database... Is the second query also working for you? Do you have 10g database, where you can try?

Quote:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

AND

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod


Re: WITH Clause with Full Outer Join [message #348206 is a reply to message #348197] Tue, 16 September 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with datav as
  2   (select * from test_outer)
  3   select nvl(dit.col2, sit.col2) val1
  4   FROM
  5   (select * from datav
  6   where col1 = 'DIT'
  7   ) DIT
  8   FULL OUTER JOIN
  9   (select * from datav
 10   where col1 = 'SIT'
 11   ) SIT
 12   ON DIT.col2 = SIT.col2
 13   FULL OUTER JOIN
 14   (select * from datav
 15   where col1 = 'UAT'
 16   ) UAT
 17   ON DIT.col2 = UAT.col2;
VAL1
----------
1
2
3
4

4 rows selected.

SQL> @v

Version Oracle : 10.2.0.4.0

SQL> with datav as
  2   (select * from test_outer)
  3   select nvl(dit.col2, sit.col2) val1
  4   FROM
  5   (select * from datav
  6   where col1 = 'DIT'
  7   ) DIT
  8   FULL OUTER JOIN
  9   (select * from datav
 10   where col1 = 'SIT'
 11   ) SIT
 12   ON DIT.col2 = SIT.col2
 13   FULL OUTER JOIN
 14   (select * from datav
 15   where col1 = 'UAT'
 16   ) UAT
 17   ON DIT.col2 = UAT.col2;
 (select * from test_outer)
*
ERROR at line 2:
ORA-00942: table or view does not exist


SQL> @v


Version Oracle : 10.2.0.1.0

Seems a 10.2.0.1 bug => upgrade.

Regards
Michel
Re: WITH Clause with Full Outer Join [message #348211 is a reply to message #348197] Tue, 16 September 2008 00:49 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Quote:
Seems a 10.2.0.1 bug => upgrade.


Sad
Re: WITH Clause with Full Outer Join [message #348214 is a reply to message #348211] Tue, 16 September 2008 01:03 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also open a SR and ask if there is one-off patch for your version and problem.

Regards
Michel
Previous Topic: Comma Seperated String (merged)
Next Topic: PLS-00201 Error
Goto Forum:
  


Current Time: Sun Dec 04 18:53:54 CST 2016

Total time taken to generate the page: 0.09825 seconds