Icon View Incident Report

Serious Serious
Reported By: Roland Verrinder
Reported On: 9/29/2005
For: Version 4.21 Build 10
# 2116 TOP Clause Mixed with ORDER BY On Expression Column Causes Incorrect Results

I need to find the last value in a sequence of Asset IDs which may be in any alpha numeric format. The following example is based on a series with an "A######" format where there will be between 1 and 6 numbers. There are other formats in the same table involving additional alpha characters eg. "AER#####" so I need ensure I filter for only the "A######" format
before extracting the numeric part to order by.

To do this, I have constructed the following SQL which extracts the integer part of the ID to order by. This returns an ID of A0 where I expect an ID of A021225, both being valid IDs.

If I remove the TOP clause to view the entire set, the dataset is ordered correctly. Changes to the WHERE clause and removal of the DESC parameter makes no difference.

SELECT AssetID, CAST(SUBSTRING(AssetID FROM 2) AS Integer)AS IntPart
FROM AssetPrimaryDetails 
WHERE AssetID LIKE 'A%' AND SUBSTRING(AssetID FROM 2 FOR 1) IN('0','1','2','3','4','5','6','7','8','9')
ORDER BY IntPart DESC TOP 1



Resolution Resolution
Fixed Problem on 10/6/2005 in version 4.21 build 11


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source

Image