Hi,

I have a view "vw_a" and here is what I want on the result

SELECT COUNT(fID) AS OrderCnt, cNm, cID, SUM(cTot) FROM vw_a WHERE oDt (BETWEEN '05/01/2005' AND '05/01/2005') GROUP BY cNm, cID

How does llblgen handles that.

Thanks, Thai

What have you tried yourself and which failed? It's perhaps you're stuck in a particular area and I can point you to the information which solves your problem. I can also write the code for you, but it's better to do it yourself, and I need from you what you're using: Adapter or selfservicing, as the solutions for this are different for adapter and selfservicing...

Otis wrote:

What have you tried yourself and which failed? It's perhaps you're stuck in a particular area and I can point you to the information which solves your problem. I can also write the code for you, but it's better to do it yourself, and I need from you what you're using: Adapter or selfservicing, as the solutions for this are different for adapter and selfservicing...

Otis,

Thanks for the reply and you do not have to write the code for me. Show me how to fish instead of give me the fish.

I can not seem to find help on the aggregation and grouping for TypeView using selfservicing. I am just started to use llblgen for a couple of days.

If you can give me directions or docs to read up on, I would appreciated.

thanks,

Otis wrote:

What have you tried yourself and which failed? It's perhaps you're stuck in a particular area and I can point you to the information which solves your problem. I can also write the code for you, but it's better to do it yourself, and I need from you what you're using: Adapter or selfservicing, as the solutions for this are different for adapter and selfservicing...

Otis, I am using Selfservicing.

Here is the code:

ResultsetFields fields = new ResultsetFields(5);
            fields.DefineField(OrderDetailFieldIndex.CustomerId, 3, AggregateFunction.None);
            fields.DefineField(OrderDetailFieldIndex.CompanyName, 0, AggregateFunction.None);
            fields.DefineField(OrderDetailFieldIndex.OrderId, 4, AggregateFunction.Count);
            fields.DefineField(OrderDetailFieldIndex.TotalSpent, 1, AggregateFunction.Sum);
            fields.DefineField(OrderDetailFieldIndex.DateChanged, 2, AggregateFunction.Max);
            
            fields["OrderId"].AggregateFunctionToApply = AggregateFunction.Count;
            fields["TotalSpent"].AggregateFunctionToApply = AggregateFunction.Sum;
            fields["DateChanged"].AggregateFunctionToApply = AggregateFunction.Max;

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields["CustomerId"]);
            groupByClause.Add(fields["CompanyName"]);

Column 'dbo.vw_OrderDetail.TotalSpent' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'dbo.vw_OrderDetail.Date_changed' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'dbo.vw_OrderDetail.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I think this code should do more:

// your usage of DefineField doesn't work, you've to specify an alias.
// see the reference manual on DefineField
ResultsetFields fields = new ResultsetFields(5);
fields.DefineField(OrderDetailFieldIndex.CompanyName, 0, "CompanyName");
fields.DefineField(OrderDetailFieldIndex.TotalSpent, 1, "TotalSpent", string.Empty, AggregateFunction.Sum);
fields.DefineField(OrderDetailFieldIndex.DateChanged, 2, "DateChanged", string.Empty, AggregateFunction.Max);
fields.DefineField(OrderDetailFieldIndex.CustomerId, 3, "CustomerId");
fields.DefineField(OrderDetailFieldIndex.OrderId, 4, "OrderId", string.Empty, AggregateFunction.Count);

IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields["CustomerId"]);
groupByClause.Add(fields["CompanyName"]);

Otis wrote:

I think this code should do more:

// your usage of DefineField doesn't work, you've to specify an alias.
// see the reference manual on DefineField
ResultsetFields fields = new ResultsetFields(5);
fields.DefineField(OrderDetailFieldIndex.CompanyName, 0, "CompanyName");
fields.DefineField(OrderDetailFieldIndex.TotalSpent, 1, "TotalSpent", string.Empty, AggregateFunction.Sum);
fields.DefineField(OrderDetailFieldIndex.DateChanged, 2, "DateChanged", string.Empty, AggregateFunction.Max);
fields.DefineField(OrderDetailFieldIndex.CustomerId, 3, "CustomerId");
fields.DefineField(OrderDetailFieldIndex.OrderId, 4, "OrderId", string.Empty, AggregateFunction.Count);

IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields["CustomerId"]);
groupByClause.Add(fields["CompanyName"]);

Otis, does this work on a selfservicing + TypedView? The samples and instructions I found so far only works in TypedList.

Thanks,

This creates a dynamic list, so a list based on a typed view, which is what you needed , it creates a query using the meta data of the typed view, and the results are stored in a normal datatable. Please see the typedlist/view documentation, for a discussion on dynamic lists. You'll use the TypedListDAO object to retrieve the data, which is ok, typed views are also fetched using that same code

Otis wrote:

This creates a dynamic list, so a list based on a typed view, which is what you needed , it creates a query using the meta data of the typed view, and the results are stored in a normal datatable. Please see the typedlist/view documentation, for a discussion on dynamic lists. You'll use the TypedListDAO object to retrieve the data, which is ok, typed views are also fetched using that same code

OK, on the designer, I added entities for TypedView and be able to clear the errors. Still, the fields that I defined still not being aggregated.

Thanks,

tvuong wrote:

Otis wrote:

This creates a dynamic list, so a list based on a typed view, which is what you needed , it creates a query using the meta data of the typed view, and the results are stored in a normal datatable. Please see the typedlist/view documentation, for a discussion on dynamic lists. You'll use the TypedListDAO object to retrieve the data, which is ok, typed views are also fetched using that same code

OK, on the designer, I added entities for TypedView and be able to clear the errors. Still, the fields that I defined still not being aggregated. Thanks,

Ah, I think I understand what's the problem. (pfew ) First I'd like to clear up something. Your code has lines like: fields.DefineField(OrderDetailFieldIndex.CustomerId, 3, AggregateFunction.None); and the 3rd argument is always the name of the field, so I don't understand how that could have compiled.

Now, what you've to do is either: - create a typedview in LLBLGen Pro designer for the view vw_a OR - create an entity mapped on view vw_a

Call that typedview or entity 'VwA'. (just for the sake of the example). You shouldn't confuse typed list with typed view.

Ok, then use this code:

// in your query you had 4 fields.
ResultsetFields fields = new ResultsetFields(4);
// SELECT COUNT(fID) AS OrderCnt, cNm, cID, SUM(cTot) AS TotalSpent
fields.DefineField(VwAFieldIndex.FID, 0, "OrderCnt", string.Empty, AggregateFunction.Count);
fields.DefineField(VwAFieldIndex.CNm, 0, "cNm");
fields.DefineField(VwAFieldIndex.CID, 3, "cId");
fields.DefineField(VwAFieldIndex.CTot, 1, "TotalSpent", string.Empty, AggregateFunction.Sum);

// Create group by collection, first cNm then cId
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields["cNm"]);
groupByClause.Add(fields["cId"]);

// then create the between filter.
PredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.Between(VwAFieldIndex.ODt, DateTime(1, 5, 2005), DateTime(2, 5, 2005)));

// then use TypedListDAO.GetMultiAsDataTable() to fetch it in a datatable.

Otis wrote:

tvuong wrote:

Otis wrote:

This creates a dynamic list, so a list based on a typed view, which is what you needed , it creates a query using the meta data of the typed view, and the results are stored in a normal datatable. Please see the typedlist/view documentation, for a discussion on dynamic lists. You'll use the TypedListDAO object to retrieve the data, which is ok, typed views are also fetched using that same code

OK, on the designer, I added entities for TypedView and be able to clear the errors. Still, the fields that I defined still not being aggregated. Thanks,

Ah, I think I understand what's the problem. (pfew ) First I'd like to clear up something. Your code has lines like: fields.DefineField(OrderDetailFieldIndex.CustomerId, 3, AggregateFunction.None); and the 3rd argument is always the name of the field, so I don't understand how that could have compiled.

Now, what you've to do is either: - create a typedview in LLBLGen Pro designer for the view vw_a OR - create an entity mapped on view vw_a

Call that typedview or entity 'VwA'. (just for the sake of the example). You shouldn't confuse typed list with typed view.

Ok, then use this code:

// in your query you had 4 fields.
ResultsetFields fields = new ResultsetFields(4);
// SELECT COUNT(fID) AS OrderCnt, cNm, cID, SUM(cTot) AS TotalSpent
fields.DefineField(VwAFieldIndex.FID, 0, "OrderCnt", string.Empty, AggregateFunction.Count);
fields.DefineField(VwAFieldIndex.CNm, 0, "cNm");
fields.DefineField(VwAFieldIndex.CID, 3, "cId");
fields.DefineField(VwAFieldIndex.CTot, 1, "TotalSpent", string.Empty, AggregateFunction.Sum);

// Create group by collection, first cNm then cId
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields["cNm"]);
groupByClause.Add(fields["cId"]);

// then create the between filter.
PredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.Between(VwAFieldIndex.ODt, DateTime(1, 5, 2005), DateTime(2, 5, 2005)));

// then use TypedListDAO.GetMultiAsDataTable() to fetch it in a datatable.

What about OrderDetailTypeView list = New OrderDetailTypedView(); list.fill();

Does that work?

thanks

[quotenick="tvuong"]

Otis wrote:

tvuong wrote:

Otis wrote:

This creates a dynamic list, so a list based on a typed view, which is what you needed , it creates a query using the meta data of the typed view, and the results are stored in a normal datatable. Please see the typedlist/view documentation, for a discussion on dynamic lists. You'll use the TypedListDAO object to retrieve the data, which is ok, typed views are also fetched using that same code

OK, on the designer, I added entities for TypedView and be able to clear the errors. Still, the fields that I defined still not being aggregated. Thanks,

Ah, I think I understand what's the problem. (pfew ) First I'd like to clear up something. Your code has lines like: fields.DefineField(OrderDetailFieldIndex.CustomerId, 3, AggregateFunction.None); and the 3rd argument is always the name of the field, so I don't understand how that could have compiled.

Now, what you've to do is either: - create a typedview in LLBLGen Pro designer for the view vw_a OR - create an entity mapped on view vw_a

Call that typedview or entity 'VwA'. (just for the sake of the example). You shouldn't confuse typed list with typed view.

Ok, then use this code:

// in your query you had 4 fields.
ResultsetFields fields = new ResultsetFields(4);
// SELECT COUNT(fID) AS OrderCnt, cNm, cID, SUM(cTot) AS TotalSpent
fields.DefineField(VwAFieldIndex.FID, 0, "OrderCnt", string.Empty, AggregateFunction.Count);
fields.DefineField(VwAFieldIndex.CNm, 0, "cNm");
fields.DefineField(VwAFieldIndex.CID, 3, "cId");
fields.DefineField(VwAFieldIndex.CTot, 1, "TotalSpent", string.Empty, AggregateFunction.Sum);

// Create group by collection, first cNm then cId
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields["cNm"]);
groupByClause.Add(fields["cId"]);

// then create the between filter.
PredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.Between(VwAFieldIndex.ODt, DateTime(1, 5, 2005), DateTime(2, 5, 2005)));

// then use TypedListDAO.GetMultiAsDataTable() to fetch it in a datatable.

OK, I have a TypedView created for vw_a in the designer.

using Reporter.DAL;
using Reporter.DAL.DaoClasses;
using Reporter.DAL.HelperClasses;
using Reporter.DAL.TypedViewClasses;
using Reporter.DAL.FactoryClasses;

and the ResultsetFields has only 3 argument.

• field to define

• index

• and agg function

thanks,

Otis wrote:

tvuong wrote:

Otis wrote:

This creates a dynamic list, so a list based on a typed view, which is what you needed , it creates a query using the meta data of the typed view, and the results are stored in a normal datatable. Please see the typedlist/view documentation, for a discussion on dynamic lists. You'll use the TypedListDAO object to retrieve the data, which is ok, typed views are also fetched using that same code

OK, on the designer, I added entities for TypedView and be able to clear the errors. Still, the fields that I defined still not being aggregated. Thanks,

Ah, I think I understand what's the problem. (pfew ) First I'd like to clear up something. Your code has lines like: fields.DefineField(OrderDetailFieldIndex.CustomerId, 3, AggregateFunction.None); and the 3rd argument is always the name of the field, so I don't understand how that could have compiled.

Now, what you've to do is either: - create a typedview in LLBLGen Pro designer for the view vw_a OR - create an entity mapped on view vw_a

Call that typedview or entity 'VwA'. (just for the sake of the example). You shouldn't confuse typed list with typed view.

Ok, then use this code:

// in your query you had 4 fields.
ResultsetFields fields = new ResultsetFields(4);
// SELECT COUNT(fID) AS OrderCnt, cNm, cID, SUM(cTot) AS TotalSpent
fields.DefineField(VwAFieldIndex.FID, 0, "OrderCnt", string.Empty, AggregateFunction.Count);
fields.DefineField(VwAFieldIndex.CNm, 0, "cNm");
fields.DefineField(VwAFieldIndex.CID, 3, "cId");
fields.DefineField(VwAFieldIndex.CTot, 1, "TotalSpent", string.Empty, AggregateFunction.Sum);

// Create group by collection, first cNm then cId
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields["cNm"]);
groupByClause.Add(fields["cId"]);

// then create the between filter.
PredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.Between(VwAFieldIndex.ODt, DateTime(1, 5, 2005), DateTime(2, 5, 2005)));

// then use TypedListDAO.GetMultiAsDataTable() to fetch it in a datatable.

Otis, does GetMultiAsDataTable requires relations?

Thanks,

tvuong wrote:

What about OrderDetailTypeView list = New OrderDetailTypedView(); list.fill();

Does that work?

Yes, but that simply fills the typed view, as in: SELECT * FROM OrderDetail. You wanted aggregates and a group by.

tvuong wrote:

OK, I have a TypedView created for vw_a in the designer.

using Reporter.DAL;
using Reporter.DAL.DaoClasses;
using Reporter.DAL.HelperClasses;
using Reporter.DAL.TypedViewClasses;
using Reporter.DAL.FactoryClasses;

and the ResultsetFields has only 3 argument.

• field to define

• index

• and agg function

thanks,

Ok, my fault, the DefineField overloads for typedview fields indeed use 3 parameters. I looked at the definefield overloads for entity fields.

For your group by collection above, please use the index in the ResultsetFields to identity the grouped columns.

tvuong wrote:

Otis, does GetMultiAsDataTable requires relations?

Thanks,

No, you can specify null if you want to.

Otis wrote:

tvuong wrote:

Otis, does GetMultiAsDataTable requires relations?

Thanks,

No, you can specify null if you want to.

As different things apparently get discussed here at once, I think it's wise to take a step back and focus what's not working and what you want to achieve, so if you have ran into an issue with the code, I can try to reproduce it here and we know we're talking about the same things. Sorry for this confusion.

Otis wrote:

Otis wrote:

tvuong wrote:

Otis, does GetMultiAsDataTable requires relations?

Thanks,

No, you can specify null if you want to.

---

As different things apparently get discussed here at once, I think it's wise to take a step back and focus what's not working and what you want to achieve, so if you have ran into an issue with the code, I can try to reproduce it here and we know we're talking about the same things. Sorry for this confusion.

Otis, I have it working smoothly. Thank you VERY MUCH for the help and your responses.

Regards,

Otis wrote:

Ok, my fault, the DefineField overloads for typedview fields indeed use 3 parameters. I looked at the definefield overloads for entity fields...

Why is the DefineField overload for typedview fields different to those available for entity fields?

When implementing this kind of functionality within our application, I have added additional overloads for the typed views - are there any reasons why this is not a good idea?

A typed view doesn't have relations, so all fields are in the same target, the view, so aliases etc. for entities aren't required.

Otis wrote:

A typed view doesn't have relations, so all fields are in the same target, the view, so aliases etc. for entities aren't required.

Ok - that makes sense...

However, aliases for fields are required (or useful at any rate) within views - especially for calculated columns.

How about the following:

Current:

    public void DefineField(xxxFieldIndex fieldToDefine, int indexInResultset)
    {
        DefineField(fieldToDefine, indexInResultset, AggregateFunction.None);
    }

    public void DefineField(xxxFieldIndex fieldToDefine, int indexInResultset, AggregateFunction aggregateFunctionToApply)
    {
        IEntityField fieldToAdd = EntityFieldFactory.Create(fieldToDefine); 
        fieldToAdd.AggregateFunctionToApply = aggregateFunctionToApply;
        base[indexInResultset] = fieldToAdd;
    }

    public void DefineField(xxxFieldIndex fieldToDefine, int indexInResultset)
    {
        DefineField(fieldToDefine, indexInResultset, AggregateFunction.None);
    }

    public void DefineField
    (
        xxxFieldIndex fieldToDefine, 
        int indexInResultset, 
        AggregateFunction aggregateFunctionToApply
    )
    {
        DefineField(fieldToDefine, indexInResultset, string.Empty, aggregateFunctionToApply);
    }

    public void DefineField
    (
        xxxFieldIndex fieldToDefine, 
        int indexInResultset, 
        string alias
    )
    {
        DefineField(fieldToDefine, indexInResultset, alias, AggregateFunction.None);
    }

    public void DefineField
    (
        xxxFieldIndex fieldToDefine,
        int indexInResultset,
        string alias,
        AggregateFunction aggregateFunctionToApply
    )
    {
        IEntityField fieldToAdd = EntityFieldFactory.Create(fieldToDefine); 
        fieldToAdd.Name = alias;
        fieldToAdd.AggregateFunctionToApply = aggregateFunctionToApply;
        base[indexInResultset] = fieldToAdd;
    }

Regards,

Dan

The alias is used more or less to make sure that duplicate columns are distinguishable, and in a view that's not needed. Of course, if you want to give a column a name, and it's not named in your view, you have a problem, that is, if you create a dynamic list from the view. A typed view has named columns.

I'll add an overload for you to that class.