Re: Outer join problem

From: VC <boston103_at_hotmail.com>
Date: Mon, 29 Aug 2005 19:53:02 -0400
Message-ID: <6b2dnfQCu8lGAo7eRVn-ow@comcast.com>

Hi,

<gabriel.gibaut_at_gmail.com> wrote in message news:1125336158.166977.196110_at_z14g2000cwz.googlegroups.com...
>I know the Select * will provide more than enough errors stating
> 'column ambiguously defined'. But my problem is not related to it, i
> was just triyng to simplify things.
>
> So:
>
> Error: ORA-01417 A table may be outer joined to at most one other
> table.
> Oracle Version: 8.1.6.0
>
> The simplified structure with just the fields involved is the
> following:
>
> Table A
> ID_TableA PK
> AttribXYZ NULL
>
> Table B
> Id_TableB PK
>
> Table C
> ID_TableA PK (references TAbleA.ID_TableA)
> ID_TableB PK (references TableB.ID_TableB)
>
> Table D
> AtribXYZ PK (References Table A.AttribXYZ)
> ID_TableB PK (References TableB.IdTableB)
>
> SELECT * FROM A, C, B, D
> WHERE
> C.IDTableA = A.IdTableA AND
> C.IdTableB = B.IdTableB AND
> A.AttribXYZ = D.AttribXYZ (+) AND
> B.IdTableB = D.IdTableB (+)
>
> Thanks
>
> Gabriel
>

Without thinking whether your query makes sense, you can bypass the problem (in 817 you cannot use the ANSI syntax) by using a subquery like this:

select * from
  (SELECT a.idtablea, b.idtableb, a.attribxyz attra, d.attribxyz attrd FROM A, C, B, D
   WHERE

    C.IDTableA = A.IdTableA        AND
    C.IdTableB =  B.IdTableB        AND
    A.AttribXYZ = D.AttribXYZ (+) ) X,

  D
where x.IdTableB = D.IdTableB (+) Received on Mon Aug 29 2005 - 18:53:02 CDT