Re: Outer Join Problem with columns from Multiple tables

From: Martin Douglas <Martin.Douglas_at_Boeing.com>
Date: Thu, 19 Aug 1999 14:50:25 GMT
Message-ID: <37BC19B1.C2B373FF@Boeing.com>

Consider the following:

My tables...

CREATE TABLE ZZZ_ITEM_SUPP(
  SUPP_CODE INTEGER,
  ITEM_CODE INTEGER,
  DESCR VARCHAR2(2000)
);

CREATE TABLE ZZZ_PO(
  PO_TXN_REF INTEGER,
  SUPP_CODE INTEGER,
  ITEM_CODE INTEGER
);

CREATE TABLE ZZZ_POL(
  POL_TXN_REF INTEGER,
  POL_NUM INTEGER
);

INSERT INTO XXX_ITEM_SUPP VALUES(4, 40, 'REMARK D'); COMMIT;

INSERT INTO XXX_PO VALUES(1000, 1, 10);
INSERT INTO XXX_PO VALUES(2000, 2, 20);
INSERT INTO XXX_PO VALUES(3000, 3, 30);
INSERT INTO XXX_PO VALUES(4000, 4, 40);

COMMIT;

INSERT INTO XXX_POL VALUES(1000, 11);
INSERT INTO XXX_POL VALUES(2000, 22);
INSERT INTO XXX_POL VALUES(3000, 33);
INSERT INTO XXX_POL VALUES(4000, 44);

COMMIT; SQLWKS> desc zzz_item_supp

Column Name                    Null?    Type
------------------------------ -------- ----
SUPP_CODE                               NUMBER(38)
ITEM_CODE                               NUMBER(38)
DESCR                                   VARCHAR2(2000)

SQLWKS> desc zzz_po
Column Name                    Null?    Type
------------------------------ -------- ----
PO_TXN_REF                              NUMBER(38)
SUPP_CODE                               NUMBER(38)
ITEM_CODE                               NUMBER(38)

SQLWKS> desc zzz_pol
Column Name                    Null?    Type
------------------------------ -------- ----
POL_TXN_REF                             NUMBER(38)
POL_NUM                                 NUMBER(38)

My broader query...

SELECT
  ZZZ_PO.PO_TXN_REF,
  ZZZ_POL.POL_NUM,
  NVL(ZZZ_ITEM_SUPP.DESCR, '<none>')
FROM

  ZZZ_PO,
  ZZZ_POL,
  ZZZ_ITEM_SUPP

WHERE
  ZZZ_PO.PO_TXN_REF = ZZZ_POL.POL_TXN_REF AND
  ZZZ_PO.SUPP_CODE = ZZZ_ITEM_SUPP.SUPP_CODE(+) AND
  ZZZ_PO.ITEM_CODE = ZZZ_ITEM_SUPP.ITEM_CODE(+); PO_TXN_REF POL_NUM
NVL(ZZZ_ITEM_SUPP.DESCR,'<NONE>') ---------- ----------

---

      1000         11
<none>                                                                          
      2000         22
<none>                                                                          
      3000         33
<none>                                                                          
      4000         44 REMARK
D                                                                        

4 rows selected.

My reduced query...

SELECT
  ZZZ_PO.PO_TXN_REF,
  ZZZ_POL.POL_NUM,
  NVL(ZZZ_ITEM_SUPP.DESCR, '<none>')
FROM

  ZZZ_PO,
  ZZZ_POL,
  ZZZ_ITEM_SUPP

WHERE
  ZZZ_PO.PO_TXN_REF = ZZZ_POL.POL_TXN_REF AND
  ZZZ_PO.SUPP_CODE = ZZZ_ITEM_SUPP.SUPP_CODE(+) AND
  ZZZ_PO.ITEM_CODE = ZZZ_ITEM_SUPP.ITEM_CODE(+) AND
  ZZZ_PO.PO_TXN_REF = 2000
;

PO_TXN_REF POL_NUM
NVL(ZZZ_ITEM_SUPP.DESCR,'<NONE>') ---------- ----------

---

      2000         22
<none>                                                                          

1 row selected.

As to the ORA-01417 you received, your query works on my system! What version do you have???

SELECT L.PO_TXN_REF, L.POL_NUM, C.DESCR FROM ZZZ_ITEM_SUPP C, (SELECT PO.PO_TXN_REF, POL.POL_NUM, SUPP_CODE, ITEM_CODE
FROM ZZZ_PO PO, ZZZ_POL POL
WHERE PO.PO_TXN_REF = POL.POL_TXN_REF) L WHERE L.SUPP_CODE = C.SUPP_CODE(+)
AND L.ITEM_CODE = C.ITEM_CODE(+)
AND L.PO_TXN_REF = 2000; PO_TXN_REF POL_NUM
DESCR ---------- ----------

---

      2000        
22                                                                                 

1 row selected.

I ran it on Oracle 7.3.4.4.1.

Probably another Oracle8 bug. :)

Best Regards...

Paul Dyer (E-mail) wrote:
>
> we need to do outer joins to a table using fields from two different
> tables.
> e.g we have a table called ZZZ_ITEM_SUPP which we need to do an
> outer join to using SUPP_CODE from ZZZ_PO and ITEM_CODE from ZZZ_POL.
>
> SQL server has no problem with this but ORACLE gives error
> (ORA-01417 a table may be outer joined to at most one other table)
>
> the only statement I have been able to come up with so far for ORACLE is
> :-
>
> SELECT L.PO_TXN_REF, L.POL_NUM, C.DESCR
> FROM ZZZ_ITEM_SUPP C, (SELECT PO.PO_TXN_REF, POL.POL_NUM, SUPP_CODE,
> ITEM_CODE
> FROM ZZZ_PO PO, ZZZ_POL POL
> WHERE PO.PO_TXN_REF = POL.PO_TXN_REF) L
> WHERE L.SUPP_CODE = C.SUPP_CODE(+)
> AND L.ITEM_CODE = C.ITEM_CODE(+)
> AND L.PO_TXN_REF = 'PO-MARG01'
>
> As you can see this means doing a sub-select thus reducing performance
> to achieve the desired result.
>
> Is there a better way of doing this ?
Received on Thu Aug 19 1999 - 09:50:25 CDT