LLBLGen Pro. Version: 2.6 Final (May 15th, 2009)

Hi,

I'm not quite sure how to sort an entity collection by an alias that have values derived from different colums based on condition.

So I'm trying to do something like this in llbl.

SELECT CustomerName, COALESCE(BillingAddress, HomeAddress) AS Address
FROM CUSTOMERS
ORDER BY Address

Can it be done during the fetch call?

Thank you.

You need to investigate http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_dbfunctioncall.htm which will allow you to use the COALESCE call, and http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_expressionsaggregates.htm which will allow you to use the call in an Order By clause.

Matt

Sorry! I think my previous example was incorrect.

What I want to do was to return an entity collection while sorted by a database function. So I want to call a database function in the ORDER BY and not an alias from the SELECT.

SELECT *
FROM Customer
ORDER BY COALESCE(BillingAddress, HomeAddress)

When calling FetchEntityCollection in the sort expression I want to put

new SortExpression("COALESCE function call") in the ISortExpression parameter.

You should use

yourSortClause.EmitAliasForExpressionAggregateField = false;

Here is an example: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=17099&StartAtMessage=0&#95782