From: Becker, Holger (holger.beckersap.com)
Date: Tue Mar 06 2007 - 08:06:10 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]