LLBL GEN PRO 3.1 Select distinct two fields

Question

Is it posible to use DefineField and AggregateFunction.SumDistinct with two fields in LLBL GenPro 3.1? For example I have this:

ResultsetFields myFields = new ResultsetFields(2);

//I want this amount to be distinct but with specific userId
myFields.DefineField(UserFields.Amount, 0, AggregateFunction.SumDistinct); 
myFields.DefineField(UserFields.Id, 1, "ResultCount", AggregateFunction.CountDistinct);

In SQL, I want it end up with something like:

SELECT DISTINCT SUM(u.Amount), u.Id 
FROM Users u
  INNER JOIN someOtherTablesat ON sot.UserId = u.Id
GROUP BY 
  u.Id

This join I have in LLBL and it duplicates amount, because someOtherTable has few data for same user. I need that join for some another reason. So, is it possible to do that with LLBL and how?

score 0 · Accepted Answer · answered Dec 05 '17 at 09:23

Finally I found solution:

var fields = new ResultsetFields(1);
fields.DefineField(OrderFields.Freight, 0, AggregateFunction.Sum);

var bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
    OrderFields.OrderId, null, OrderDetailFields.OrderId, null, 
    SetOperator.In, (OrderDetailFields.ProductId ==1)));

DataTable dynamicList = new DataTable();

using (var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, dynamicList, bucket);
}

In SQL that is more like

Select SUM(Freight) from Orders
Where OrderID IN 
(
    Select OrderID from [Order Details] where ProductID = 1
)

LLBL GEN PRO 3.1 Select distinct two fields

1 Answers1