Re: outer join not working?

Not being able to see your data there are many possibilities. Part of the problem here is that your test case is too complex. Drop back to just a two table join and then add the others in incrementally ... testing at each step along the way.

Daniel Morgan

Michael Ragsdale wrote:

> 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 - 13:15:25 CST