AW: Re: "order by " problem with 7.6.00.34 2007-02-14 - By H?bschen, Peter
Back
> -- --Urspr?ngliche Nachricht-- --
> Von: Becker, Holger [mailto:holger.becker@(protected)]
> Gesendet: Mittwoch, 14. Februar 2007 08:19
> An: H?bschen, Peter
> Cc: maxdb@(protected); Robert Klemme
> Betreff: RE: [LIKELY JUNK]Re: "order by" problem with 7.6.00.34
>
> > -- --Original Message-- --
> > From: Robert Klemme [mailto:shortcutter@(protected)]
> > Sent: Dienstag, 13. Februar 2007 22:51
> > To: H?bschen, Peter
> > Cc: maxdb@(protected)
> > Subject: [LIKELY JUNK]Re: "order by" problem with 7.6.00.34
> >
> > 2007/2/13, H?bschen, Peter <Peter.Huebschen@(protected)>:
> > > Hello,
> > >
> > > I have a strange problem with an "order by"-clause, which
> > doesn't sort as expected. First it appeared in a stored procedure
> > which works correctly with SAPDB 7.4 on Linux. For testing
> I created a
> > local MaxDB-Installation on my PC (WinXP
> > Pro.) with MaxDB 7.6.00.34 and imported a current backup
> from my 7.4.
> > database. The "order by" clause looks a little bit
> "strange", but due
> > to an old design failure I had to go this way.
> > > Here's the Select-Statement:
> > >
> > > SELECT tbl_Anfrage.Anfragenummer,
> > > tblKunden.Kundenname
> > > FROM tblKunden,
> > > tbl_Anfrage,
> > > tbl_Teile
> > > WHERE tblKunden.Kunden_ID (+) = tbl_Anfrage.Kunden_ID
> > > AND tbl_Anfrage.lfd_Anfragenummer > > tbl_Teile.lfd_Anfragenummer (+)
> > > AND tbl_Anfrage.Anfragenummer Like 'D-%/07'
> > > GROUP BY tbl_Anfrage.Anfragenummer,
> > > tblKunden.Kundenname,
> > > tbl_Anfrage.Anlagedatum
> > > ORDER BY
> > >
> > SUBSTR(Anfragenummer,1,2)||substr(Anfragenummer,6,2)||'0'||sub
> > str(Anfragenummer,3,2) desc
> > >
> > > The output looks like this:
> > > D-01/07 XYZ
> > > D-03/07 ABC
> > > D-05/07 EFG
> > > D-02/07 HIJ
> > > D-06/07 MNO
> > > D-04/07 STV
> > >
> > > correctly it should be:
> > > D-06/07 MNO
> > > D-05/07 EFG
> > > D-04/07 STV
> > > D-03/07 ABC
> > > D-02/07 HIJ
> > > D-01/07 XYZ
> > >
> > > If I add
> > SUBSTR(Anfragenummer,1,2)||substr(Anfragenummer,6,2)||'0'||sub
> > str(Anfragenummer,3,2) so it is displayed in the result and in the
> > group by clause, it is ordered correctly, but I get some duplicate
> > rows of D-01/07. So I don't know where the problem is and I found
> > nothing in the documenation
> >
> > One strange thing: you group by a tbl_Anfrage.Anlagedatum
> but do not
> > show it anywhere. This might be responsible for duplicates
> although I
> > fail to understand how then you don't see the duplicates in your
> > original query (i.e. the one where you did not add the SUBSTR...).
> >
> > Did you check the bugtracker? Maybe this is a known issue.
> If not I'd
> > probably try to recreate a simple test case and submit it as a bug
> > report. I know that in the past there were issues with
> joins. So one
> > thing to try could be to change the DB parameter that controls join
> > execution. For me that helped in one case in the past (7.5).
> >
> > Good luck!
> >
> > robert
>
> Hi,
>
> Roberts hint about some join problems remind me on a known
> problem with some new aggregation feature.
>
> You could enable this feature by setting database parameter
> HASHED_RESULTSET to NO.
>
> You could change the parameter in online mode. For details
> see "dbmcli help param_put".
>
> Please let me know if this solved you problem.
>
> Kind regards
> Holger
>
> 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.
>
Hello,
I've set the parameter to no and tested it, but I always get the results
mentioned above (not sorted or with duplicate rows - but sorted correctly - if
I add my "order"-string to the "output"-section).
Kind regards
Peter
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=mysql@(protected)
|
|
