RE: [LIKELY JUNK]Problem with left join select command ??

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]