Can anyone help with this select?

Posted in 1999

Respectful — Sat, 14 Aug 1999 17:19:30 -0500 — source: Informix-list mailing list archive (1991-1998)

It worked until I put the "outer outer" in it! Any suggestions? SELECT A.checkn, B.erncd , B.earnings , I.earnings_key, C.dedcd , C.ded_cur , D.tax_class , D.tax_cur FROM p_check A, # ^ # 395: The where clause contains an outer Cartesian Product. # OUTER (p_check_earns B,OUTER earnings I), OUTER p_deduction C, OUTER p_tax D WHERE A.emplid = "1014165" AND A.checkn = 155528 AND I.effdt = ( SELECT max(J.effdt) FROM earnings J WHERE J.erncd = B.erncd AND J.effdt <= A.check_dt ) AND A.company = B.company AND A.paygroup = B.paygroup AND A.pay_end_dt = B.pay_end_dt AND A.off_cycle = B.off_cycle AND A.pagen = B.pagen AND A.linen = B.linen AND A.sepchk = B.sepchk AND A.company = C.company AND A.paygroup = C.paygroup AND A.pay_end_dt = C.pay_end_dt AND A.off_cycle = C.off_cycle AND A.pagen = C.pagen AND A.linen = C.linen AND A.sepchk = C.sepchk AND A.company = D.company AND A.paygroup = D.paygroup AND A.pay_end_dt = D.pay_end_dt AND A.off_cycle = D.off_cycle AND A.pagen = D.pagen AND A.linen = D.linen AND A.sepchk = D.sepchk order by A.checkn, B.erncd , C.dedcd , D.tax_class

↪ replying to Respectful

Jonathan Leffler — Sat, 14 Aug 1999 19:54:26 -0700 — source: Informix-list mailing list archive (1991-1998)

Respectful wrote: > > It worked until I put the "outer outer" in it! Any suggestions? > > SELECT > A.checkn, > B.erncd , > B.earnings , > I.earnings_key, > C.dedcd , > C.ded_cur , > D.tax_class , > D.tax_cur > > FROM > p_check A, > # ^ > # 395: The where clause contains an outer Cartesian Product. > # > OUTER (p_check_earns B,OUTER earnings I), > OUTER p_deduction C, > OUTER p_tax D > > WHERE > A.emplid = "1014165" AND > A.checkn = 155528 AND > > I.effdt = > > ( SELECT max(J.effdt) FROM earnings J > WHERE J.erncd = B.erncd > AND J.effdt <= A.check_dt ) AND > > A.company = B.company AND > A.paygroup = B.paygroup AND > A.pay_end_dt = B.pay_end_dt AND > A.off_cycle = B.off_cycle AND > A.pagen = B.pagen AND > A.linen = B.linen AND > A.sepchk = B.sepchk AND > > A.company = C.company AND > A.paygroup = C.paygroup AND > A.pay_end_dt = C.pay_end_dt AND > A.off_cycle = C.off_cycle AND > A.pagen = C.pagen AND > A.linen = C.linen AND > A.sepchk = C.sepchk AND > > A.company = D.company AND > A.paygroup = D.paygroup AND > A.pay_end_dt = D.pay_end_dt AND > A.off_cycle = D.off_cycle AND > A.pagen = D.pagen AND > A.linen = D.linen AND > A.sepchk = D.sepchk > order by > A.checkn, > B.erncd , > C.dedcd , > D.tax_class You have join conditions linking A with each of B, C and D, but nothing linking B and I -- so there is an OUTER Cartesian Product between B and I. You need to consider whether life would be easier if you didn't have to specify 7-part joins -- use a SERIAL column in A and an INTEGER column in D, repeast for B and C too. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>