Re: "order by" problem with 7.6.00.34
"Robert Klemme" <[email protected]>
| Newsgroups | gmane.comp.db.maxdb |
|---|---|
| Message-ID | <[email protected]> |
2007/2/13, Hübschen, Peter <[email 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'||substr(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'||substr(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 -- Have a look: http://www.flickr.com/photos/fussel-foto/ -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[email protected]