Hi,

Hope that someone has an answer to the following question. How can I rebuild this SQL-statement into nice LLBLGen code (-->> This query fetches the last known transaction (on transactionDate, desc) for each involved depot (=customer)) :

SELECT dbo.[Transaction].DepotId, dbo.Depot.DepotName, dbo.[Transaction].TransactionDate, dbo.[Transaction].FlowDirection FROM dbo.Depot INNER JOIN dbo.[Transaction] ON dbo.Depot.DepotId = dbo.[Transaction].DepotId INNER JOIN dbo.DepotType ON dbo.Depot.DepotTypeId = dbo.DepotType.DepotTypeId WHERE dbo.[Transaction].TransactionDate = (SELECT TOP 1 TransactionDate FROM dbo.[Transaction] WHERE DepotId = dbo.Depot.DepotId ORDER BY TransactionDate DESC) ORDER BY dbo.[Transaction].TransactionDate

grtz, Danny

For the inner select, you should be using a FieldCompareSetPredicate predicate using maxNumberOfItemsToReturn = 1, and a desc sorter.

ALso, use the SetOperator.Equal in that case. It's more efficient than IN when the subset has just 1 element

OK, thanks Frans and Walaa...!!

It works like crazy, and here's my code :

sorter.Add(SortClauseFactory.Create(TransactionFieldIndex.TransactionDate, SortOperator.Descending))

'define depot filter for subquery : depotfilter.Add(CompareExpression(TransactionFieldIndex.DepotId, ComparisonOperator.Equal, New Expression(EntityFieldFactory.Create(DepotFieldIndex.DepotId))))

'Add the SetOperator to the filter (=SUBQUERY) and sort DESC on TransactionDate filter.Add(New FieldCompareSetPredicate(EntityFieldFactory.Create(TransactionFieldIndex.TransactionId), EntityFieldFactory.Create(TransactionFieldIndex.TransactionId), SetOperator.Equal, depotfilter, Nothing, "", 1, sorter))

result = FetchTypedList(fields, filter, relations, sorter, False, 0, Nothing)