Beermann, Albert wrote: > Hello Everybody > > > > Left join problem with maxdb 7.6.00.25 (64 bit on suse linux) > > > > I have 3 tables stpersonalstamm,lolohnkopf,lolohndaten > > The link between stpersonalstamm and lolohnkopf is stpersonalstammid > > The link between lolohnkopf and lolohndaten is lolohnkopfid > > > > > > In lolohnkopf there can be 1 to n records for a > stpersonalstammid for a > given lofuerjahr,lofuermonat but don't have to > > In lolohndaten there can be 1 to n records for a lolohnkopfid > but don't > have to > > > > I want to search all records from stpersonalstamm and find out wether > they have entries in lolohnkopf and in lolohndaten or not > > > > select * from stpersonalstamm > > left join lolohnkopf on stpersonalstamm.stpersonalstammid = > lolohnkopf.stpersonalstammid > > and lolohnkopf.lofuerjahr = ?l_jj and lolohnkopf.lofuermonat = ?l_mm > > left join lolohndaten on lolohndaten.lolohnkopfid = > lolohnkopf.lolohnkopfid > > where stpersonalstamm.stnlstammid = ?P_stnlstammid > > and stpersonalstamm.staustritt >= ?l_aktdatum > > and stpersonalstamm.steintrittnl <= ?l_lastdatum > > order by stpersonalstamm.stpersonalstammid " > > > > I expected a result cursor with all columns from stpersonalstamm , > > all columns from lolohnkopf (filled or null) and all columns from > lolohndaten (filled or null) > > > > If there are some records in lohnkopf for a given > lofuermonat,lofuerjahr > the select works as expected. > > The lolohnkopf part of the result cursor is filled with > correct data or > null values > > > > But if there are no records in lolohnkopf for a given > lofuermonat,lofuerjahr the result cursor is empty ??????????? > > > > > Any help welcomed > > > > Best regards > > Albert > >
Hi, I suppose it's a known bug within outer join handling in MaxDB. It'll be solved with one of the next patches. For details see http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1146754. Rewriting your query may help, try: select * from stpersonalstamm left join lolohnkopf on stpersonalstamm.stpersonalstammid = lolohnkopf.stpersonalstammid and lolohnkopf.lofuerjahr = ?l_jj and lolohnkopf.lofuermonat = ?l_mm left join lolohndaten on lolohnkopf.lolohnkopfid = lolohndaten.lolohnkopfid /* <<< toggle predicate !!!*/ where stpersonalstamm.stnlstammid = ?P_stnlstammid and stpersonalstamm.staustritt >= ?l_aktdatum and stpersonalstamm.steintrittnl <= ?l_lastdatum order by stpersonalstamm.stpersonalstammid I know it sounds strange but I hope it helps. Kind regards Holger Becker MaxDB&liveCache SAP AG www.sap.com Sitz der Gesellschaft/Registered Office: Walldorf, Germany Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai Agassi, Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso Plattner Registergericht/Commercial Register Mannheim No HRB 350269 Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt. Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank. This e-mail may contain trade secrets or privileged, undisclosed, or otherwise confidential information. If you have received this e-mail in error, you are hereby notified that any review, copying, or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal. Thank you for your cooperation. -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
