I've a problem while bulding a dymanic list with aggregate function and sorting clause.

ResultsetFields fields = new ResultsetFields(2);
fields.DefinedField(ItemFieldIndex.ItemNo, 0, "Item No.");
fields.DefinedField(OrderFieldIndex.Amt, 1, "Total Amt",  AggregateFunction.Sum);

ISortExpression sortExpression = new SortExpression();
sortExpression.Add(new SortClause(fields[1], null, SortOperator.Descending));

Exception thrown while fetching the list, "Incorrect syntax near 'Amt'."

SQL generated by LLBLGen:

SELECT DISTINCT [dbXXX].[dbo].[Item].[ITEM_NO] AS [Item No.],
SUM([dbXXX].[dbo].[OrderLine].[NET_ITEM_AMT]) AS [Total Amt]
FROM ( [dbXXX].[dbo].[Item] LEFT JOIN [dbXXX].[dbo].[OrderLine]  ON
[dbXXX].[dbo].[Item].[ITEM_NO]=[dbXXX].[dbo].[OrderLine].[ITEM_NO])
GROUP BY [dbXXX].[dbo].[Item].[ITEM_NO]
ORDER BY Total Amt DESC

"ORDER BY Total Amt DESC" should be "ORDER BY [Total Amt] DESC", I think there is a bug in SortClause, please check. Thanks!!!

Could you tell us please what Runtime Library version you are using!

v1.0.2005.1, 18 Nov 2005

Don't use spaces in aliases. The sort clause data is emitted in general code which means the 'alias' is used literaly. I'll see if I can solve it somehow. In the meantime: don't use spaces in the aliases.

I'm writing a program to allow user to build dymanic query based on my entities, I use "alias" as the column caption in the query result, I cannot ask clients not to use space for the caption. Please try your best to solve the problem. Thanks you very much!!!

Barry wrote:

I'm writing a program to allow user to build dymanic query based on my entities, I use "alias" as the column caption in the query result, I cannot ask clients not to use space for the caption. Please try your best to solve the problem. Thanks you very much!!!

string aliasToUse = alias.Replace(" ", "_");

Though I see what you mean. I'll see what I can do.

(edit): the error only occurs in a rare case: - the field has a space in the alias - the field is in a sortclause - the field has an expression and / or aggregate set.

In this situation, I'll emit the alias wrapper tokens ([] or quotes), in all other situations I won't as that will break code (in other rare cases ).