Re: Cartesian product?

>Date: Tue, 17 Jan 1995 12:02:50 -0800 (PST) >From: Informix Sysadmin <informix@asl3.asl-labs.bc.ca> >Subject: Cartesian product? >X-Informix-List-Id: <list.5346> > >I recently received this error message: > >Error -395 "The where clause contains an outer cartesian product" > >- What does that mean? The book isn't exactly verbose when it says: >"Check the syntax" > >The select that caused this is: > >select presult.batchno, presult.smpleno, smple.nature, smple.ordr, >presult.pmethno, presult.methno, presult.anateno, analyte.sort, >smple.descrip, smple.sdate, smple.stime, batch.wonum, smple.smpleid1, >smple.smpleid2, analyte.descrip, presult.result, presult.unts, presult.dl,>presult.dlpow >from presult, outer ( smple, batch, outer analyte) >where presult.anateno = 103 >and (presult.guyno = 0 or presult.guyno = 84) >and presult.released = 0 >and batch.batchno = presult.batchno >and smple.batchno = presult.batchno >and smple.smpleno = presult.smpleno >and analyte.anateno = presult.anateno >order by batch.wonum, smple.ordr, analyte.sort > >Any comments? I'm not sure what's wrong. The FROM clause structure indicates that: * presult will be joined to either smple or batch, * and either smple or batch will be joined to analyte. The WHERE clause joins batch and smple to presult, which is OK, but it also joins analyte to presult and not to batch and smple, which is what is causing the error message. You should look in the manual appendices for the one on complex outer joins to see something of what I mean. It is Appendix H in the ISQL User Guide (Version 4.0), Appendix G in the I4GL Reference Manual (Version 4.0). Using more or less the notation developed there, the join structure implied by the FROM clause should be: presult | | smple ----- batch | | analyte The positions of smple and batch could be switched. The join structure of the WHERE clause is: presult | ------------------------ | | | batch smple analyte To work out what you should have written, we'd need to know quite a lot about the data. Judging by the criteria you've written, I think that the FROM clause could be: FROM presult, OUTER smple, OUTER batch, OUTER analyte To justify the bracketed OUTER clause, you would need to have joins between smple and batch and analyte. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>