Calculated field in ORDER BY

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Dmitri
Posts: 9
Joined: Wed 19 Jan 2005 22:58

Calculated field in ORDER BY

Post by Dmitri » Fri 01 Aug 2008 17:32

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
Last edited by Dmitri on Wed 06 Aug 2008 16:35, edited 1 time in total.

Dmitri
Posts: 9
Joined: Wed 19 Jan 2005 22:58

Post by Dmitri » Wed 06 Aug 2008 16:33

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 08 Aug 2008 14:20

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.

Post Reply