 | | | Problem with left join select command ?? | Problem with left join select command ?? 2007-03-06 - By Becker, Holger
Back
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
46754.
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/maxdb?unsub=mysql@(protected)
|
|
|