Calculated field in ORDER BY

[Dmitri]

Hi guys,
Just moved into the very latest version 4.5 yesterday.
Previously used ver.3.80

After testing found strange behaviour:
when SQL statement contains something like that:

SELECT PeopleID, PeopleType,
PeopleTypeName =
CASE
WHEN PeopleType = 1 THEN 'Contact'
WHEN PeopleType = 2 THEN 'Vendor'
WHEN PeopleType = 3 THEN 'Customer'
ELSE 'Undefined'
END,
LastName, FirstName, ...
FROM People
ORDER BY PeopleTypeName, LastName

Such query works fine in SQL Analyser or in SMS SQL 2005, however
in DataSnap server ( we are using SDAC TMSQueries there)
failed with two problems:
1. query did not return all the results,
2. throwing exception kind of "Key violation'...

Removing PeopleTypeName from ORDER BY fixing the problem...

It was working OK in older SDAC...
What is the problem with newest release?

Best Regards

[Dmitri]

As an extesion to my previous post:
such SQL statement does have errors in execution in TMSQuery,
but does not work properly as well - sort order is incorrect:

SELECT PeopleID, PeopleType,
LastName, FirstName, ...
FROM People
ORDER BY
CASE
WHEN PeopleType = 1 THEN 'Contact'
WHEN PeopleType = 2 THEN 'Vendor'
WHEN PeopleType = 3 THEN 'Customer'
ELSE 'Undefined'
END, LastName

However standard SQL Server client tools are producing correct result.

[Dimon]

On query executing TMSQuery sends an SQL statement to SQL server and returns records in the same order as they are returned by the server if the TMSQuery.IndexFieldNames property is empty.

Please send a complete small sample to dmitryg*devart*com to demonstrate the problem, including a script to create and fill table.