Re: Outer join

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Tue, 17 Apr 2001 23:55:32 +0200
Message-ID: <3ADCBBD4.273C3281@0800-einwahl.de>

I created a small testcase to try to help you. However, I found that you do not exactly write what you want. Please take that testcase and bring it in order because I believe that table A is not properly defined. To enable us to help you please provide the statement in the proper syntax, the table definitions in the correct way (this testcase is sufficient for finding the syntax) and the joins as normal inner joins. *Then* specify what you want to outer join.

Martin

Here is my testcase:

SQL>
SQL> create Table A (

  2  col1a number,
  3  col2b number,
  4  col2c number,

  5 col2d number);

Table created.

SQL>
SQL> create Table B (

  2  col1b number,
  3  col2b number,
  4  col3b number,

  5 col4b number);

Table created.

SQL>
SQL> create Table C (

  2  col1c number,
  3  col2c number,
  4  col3c number,

  5 col4c number);

Table created.

SQL>
SQL> create Table D (

  2  col1d number,
  3  col2d number,
  4  col3d number,

  5 col4d number);

Table created.

SQL>
SQL> create Table E (

  2  col1a number,
  3  col2b number,
  4  col3b number,
  5  col4b number,
  6  col2c number,
  7  col3c number,
  8  col4c number,
  9  col3d number,

 10 col4d number);

Table created.

SQL>
SQL> --Notes: Table C is the parent of Table D col1x (x being a, b, c, d) is a foreign key

SQL> 
SQL> -- now I am trying to do is pull this all together (in puesdo code):
SQL> 
SQL> insert into E (
  2  col1a,
  3  col2b,
  4  col3b,
  5  col4b,
  6  col2c,
  7  col3c,

  8 col4c,
  9 col3d,
 10 col4d)
 11 select col1a, col2b, col3b, col4b, col2c, col3c, col4c, col3d, col4d

 12  	 from  A, B, C, D
 13  	   where  col1a = col1b  (+) and
 14  		      col2a = col2b  (+) and
 15  		      col1a = col1c  (+) and
 16  		      col2a = col2c  (+) and
 17  		      col1c = col1d (+) and
 18  		      col2c = col2d (+)
 19  ;
                 col2c = col2d (+)
                         *

ERROR at line 18:
ORA-00918: column ambiguously defined

SQL>
SQL> spool off Received on Tue Apr 17 2001 - 16:55:32 CDT