Count aggregate on a view.

Posts   
 
    
JayBee
User
Posts: 269
Joined: 28-Dec-2006
# Posted on: 19-Oct-2016 20:27:44   

I'm using LLBLGen 4.2 SelfServicing.

I have a view Pallet with 4 fields: Code, IdSap, Pallennummer and DateTimeCreated. I want to have a collection that is the outcome of:

select Code, IdSap, count(distinct Palletnummer) where DateTimeCreated between from and to.

The following is working fine:

        ResultsetFields fields = new ResultsetFields(3);
        fields.DefineField(PalletFields.Code, 0);
        fields.DefineField(PalletFields.IdSap, 1);
        fields.DefineField(PalletFields.Palletnummer, 2, "Pallets");
        fields[2].AggregateFunctionToApply = AggregateFunction.CountDistinct;
        PredicateExpression filter = new PredicateExpression(new FieldBetweenPredicate(PalletFields.DateTimeCreated, from, to));
        SortExpression sorter = new SortExpression(PalletFields.Code | SortOperator.Ascending);
        GroupByCollection gbc = new GroupByCollection();
        gbc.Add(PalletFields.Code);
        gbc.Add(PalletFields.IdSap);
        TypedListDAO dao = new TypedListDAO();
        DataTable results = new DataTable();

I do not think this is the most elegant wayconfused Isn't it possible to achieve via collection on the view itself, without using the result set? I also tried QuerySpec but could not get it to work. How could this be done via QuerySpec?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Oct-2016 23:01:58   

You are doing it the right way, as you are aggregating and grouping. Collections are used to retrieve entities as they exist in the database.

Please post your QuerySpec code trials, and the output.

There is an example here in docs that can give you a clear idea how to aggregate and group in QuerySpec