Re: outer join not working?

If you use outer join you must use it on that table everywhere in the WHERE clause, so add (+) to all the finventory references (all the lines marked <<<<).

When you left off the (+) you basically told oracle to get all rows where that column is NOT NULL, discarding the outer join rows from the result set.

Ed Prochak
Magic Interface Ltd.

Michael Ragsdale <mragsdal-NOSPAM-_at_utk.edu> wrote in message news:<3C7D1E51.199315C4_at_utk.edu>...
> See the code below. When I comment out the lines indicated by "<<<<", I
> get some 2300 records returned. If I add them back in, I get 441
> records. I want ALL of the customers following all other criteria to be
> returned whether they have a match in the finventory side or not. It
> seems that the (+) is not working on the following line:
> finventory.i_cuid (+) = fcustomer.cuid AND
> since that is the line connecting the fcustomer table with the
> finventory data. Am I missing something? The table relationship is:
>
> fdepartment -< >- fpermittype
> fpermitclass -< fcustomer -< finventory >- fpossstatus
> f_citations >-
>
>
>
> SELECT fpermitclass.pec_code,
> fcustomer.cuname,
> fcustomer.cussn,
> fdepartment.dcode,
> f_citations.pc_number,
> f_citations.pc_issue_date,
> f_citations.pc_balance,
> finventory.inumber
> FROM fcustomer,
> fdepartment,
> fpermitclass,
> f_citations,
> finventory,
> fpermittype,
> fpossstatus
> WHERE fcustomer.cu_did = fdepartment.did AND
> fcustomer.cu_pec_id = fpermitclass.pec_id AND
> fcustomer.cuid = f_citations.pc_cuid AND
> f_citations.pc_balance > 0 AND
> fpermitclass.pec_code in ('FT', 'FR') AND
> finventory.i_cuid (+) = fcustomer.cuid AND <<<<
> finventory.i_pt_id = fpermittype.pt_id AND <<<<
> finventory.iinvalid = 0 AND <<<<
> finventorry.i_psid = fpossstatus.psid AND <<<<
> fpossstatus.pscode = 'A' <<<<
> order by pec_code, cussn, pc_number;
Received on Wed Feb 27 2002 - 21:45:46 CST