"order by " problem with 7.6.00.34 2007-02-13 - By H?bschen, Peter
Back
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
Kind regards
Peter H?bschen
|
|
