Hello everyone,

I have a typed list that connects a few tables together including

e.g. Orders > OrderItem

OrderItem has a field "Quantity"

and, for example, I want to bring back all the orders that have a sum of the quantities greater than 10. How do i add this in without resorting to a dynamic list or worse, an SP!

hope you can help, Mike

Hmm, how would the query look like in SQL? Something with like this? -> select * from orders where orderid in ( select orderid from orderdetails group by orderid having SUM(quantity) > 10 )

?

The problem at the moment is that the FieldCompareSetPredicate can't do a groupby at the moment. (will be added soon, but that's not help you now). This means that you can't specify the inner query at the moment. If you know another way of doing this in SQL, please let me know.

Ah found another way: select o.orderid, od.productid from orders o inner join [order details] od on o.orderid = od.orderid group by o.orderid, od.productid having SUM(od.quantity) > 100

works on northwind, so this can be done in llblgen pro code What you have to do is to create a groupby collection which contains all fields in the typed list and which has a SUM predicate for the having clause on the quantity field.

If you specify what you use: selfservicing or adapter, and which fields there are in your typed list, I can write up the code for you.

(edit): hmm this seems to produce invalid results... If I remove the od.productid, it works. hmm.

Thanks for helping - here is the SQL statement that seems to work:

SELECT DISTINCT [customer].[firstname] AS [Firstname], [customer].[lastname] AS [Lastname], [customer].[email] AS [Email], [order].[dateCreated] AS [DateCreated], [discount].[ code] AS [ Code], [order].[ID] AS [ID], [order].[sent] AS [Sent], [order].[cancelled] AS [Cancelled], [order].[datecreated] AS [dateCreated], [order].[grandtotal] AS [Grandtotal], [howpaid].[text] AS [HowPaid], sum(Orderitem.Quantity) as Quantity FROM [order] LEFT JOIN [customer] ON [customer].[ID]=[order].[customerID] LEFT JOIN [discount] ON [discount].[ID]=[order].[discountID] LEFT JOIN [howpaid] ON [howpaid].[ID]=[order].[howPaid] LEFT JOIN [orderitem] ON [order].[ID]=[orderitem].[orderID] group by [order].[id], [order].[sent], [customer].[firstname], [customer].[lastname], [customer].[email], [discount].[ code], [order].[sent], [order].[cancelled], [order].[datecreated], [order].[grandtotal], [howpaid].[text] having sum(Orderitem.Quantity)>@GreaterThanQuantity ORDER BY [order].[dateCreated] DESC

If this can be translated that I would be most grateful! (I am using SelfServicing)

Mike

yes this can be transformed to llblgen pro code. I'll get back to you on this.

Hi Otis, After re-reading the "Using GroupByCollection and Having Clauses" section in the help and reading my SQL statement I managed to understand it and now it works! Thanks for putting me on the right track.

Mike

This should do the trick, of course you have to change the name of the typedlist class:

First create a derived class from your TypedList class. Make sure the constructor gets called OK from the constructor of the derived class.

Then, override BuildResultset() (in the derived typed list class)

public override IEntityFields BuildResultset()
{
    IEntityFields fields = base.BuildResultset();
    // now set the aggregate on the quantity column.
    fields["Quantity"].AggregateFunctionToApply = AggregateFunction.Sum;
    
    return fields;
}

Before calling Fill, you have to produce a groupby collection. MyTypedList is the derived class from the typed list.

MyTypedList tl = new MyTypedList();
// set obey weak relations, as you need leftjoins:
tl.ObeyWeakRelations = true;
// grab the fields for the groupby clauses:
IEntityFields fields = tl.BuildResultset();
// build the groupbycollection:
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields["ID"]);
groupBy.Add(fields["Sent"]);
groupBy.Add(fields["Firstname"]);
groupBy.Add(fields["Lastname"]);
groupBy.Add(fields["Email"]);
groupBy.Add(fields["Code"]);
groupBy.Add(fields["Sent"]);
groupBy.Add(fields["Cancelled"]);
groupBy.Add(fields["DataCreated"]);
groupBy.Add(fields["GrandTotal"]);
groupBy.Add(fields["HowPaid"]);
groupBy.HavingClause = new PredicateExpression(
    new FieldCompareValuePredicate(
        fields["Quantity"], ComparisonOperator.GreaterThan, _quantityValue));
        
tl.Fill(0, null, true, null, null, groupBy);

mikeydelamonde wrote:

Hi Otis, After re-reading the "Using GroupByCollection and Having Clauses" section in the help and reading my SQL statement I managed to understand it and now it works! Thanks for putting me on the right track.

I didn't read this posting when I posted the code hehe, oh well.. glad it works now

No problem, nothing like learning lots by struggling through yourself!