Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> outer join not working?

outer join not working?

From: Michael Ragsdale <mragsdal-NOSPAM-_at_utk.edu>
Date: Wed, 27 Feb 2002 12:58:41 -0500
Message-ID: <3C7D1E51.199315C4@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 - 11:58:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US