Hi

Struggling with knowing what the best way to acheive my goal is and wondered if someone could help..

Simple task and I have searched the forums and documentation (I belieive I am close) but cant get this to work. I have a completedorder table that I use to generate invoices, and this table contains all data I need and I currently have a Aggregate.Sum working with grouping etc so all is fine. The problem comes now that I would like to filter some of the rows I have and am getting an exception thrown in the SQL as follows:

CompletedOrder does not match with a table name or alias name used in the query.

My code is below and I am using version 2 self servicing, should I be setting the filter expression to the field directly?? Is this in fact the best way to sum and group ( I am using dynamic list because of large number of columns in my designed typedlist)

If anyone can point me in the right direction I would be very grateful. ( I have taken all my grouping and aggregates out to simplify the issue but this simplified code still exhibits the problem)

Many thanks

Richard

ResultsetFields fields = new ResultsetFields(2); fields.DefineField(CompletedOrderFields.CoDescription, 0, "Description", "CompletedOrder"); fields.DefineField(CompletedOrderFields.CoGross, 1, "Total", "CompletedOrder");

IPredicateExpression filter = new PredicateExpression(); filter.Add(CompletedOrderFields.CoInvoiceid == 0);

DataTable summaryList = new DataTable(); TypedListDAO dao = new TypedListDAO(); dao.GetMultiAsDataTable(fields, summaryList, 0, null, filter, null, false, null, null, 0, 0);

Hi rparkins, could you please paste de sql query you want to produce?

goose wrote:

Hi rparkins, could you please paste de sql query you want to produce?

Hi Goose,

Many thanks for your reply..I am trying something like this:

select p.o_id, sum(co_gross), co_description from CompletedOrder as c, ProductOrder as p where c.pro_id = p.pro_id and c.co_invoiceid = 0 group by p.o_id, c.co_description

Rich

fields.DefineField(CompletedOrderFields.CoGross, 1, "Total", "CompletedOrder");

You shouldn't use "CompletedOrder" here, as this Object aliases isn't used further in any relation (as far as I see). So let it be:

fields.DefineField(CompletedOrderFields.CoGross, 1, "Total");

If that doesn't work, Could you post the complete snippet? (I think the groupBy and the aggregate expression are missing)

daelmo wrote:

fields.DefineField(CompletedOrderFields.CoGross, 1, "Total", "CompletedOrder");

You shouldn't use "CompletedOrder" here, as this Object aliases isn't used further in any relation (as far as I see). So let it be:

fields.DefineField(CompletedOrderFields.CoGross, 1, "Total");

If that doesn't work, Could you post the complete snippet? (I think the groupBy and the aggregate expression are missing)

Hi Thanks for your help on this, complete code snippet below.

Cheers

Rich

/// <summary> /// Returns all requests that are pending and have not expired /// </summary> /// <returns></returns> public static void GetUnreconciledCompletedOrders() { ResultsetFields fields = new ResultsetFields(3); fields.DefineField(CompletedOrderFields.CoDescription, 0, "Description", "CompletedOrder"); fields.DefineField(CompletedOrderFields.CoGross, 1, "Total", "CompletedOrder"); fields.DefineField(ProductOrderFields.OId, 2, "Owner", "ProductOrder");

        IRelationCollection relations = new RelationCollection();
        relations.Add(CompletedOrderEntity.Relations.ProductOrderEntityUsingProId, "CompletedOrder", "ProductOrder", JoinHint.None);

        IGroupByCollection groupByClause = new GroupByCollection();
        groupByClause.Add(fields[2]);
        groupByClause.Add(fields[0]);

        IPredicateExpression filter = new PredicateExpression();
        filter.Add(CompletedOrderFields.CoInvoiceid == 0);

        DataTable summaryList = new DataTable();
        TypedListDAO dao = new TypedListDAO();
        dao.GetMultiAsDataTable(fields, summaryList, 0, null, filter, relations, false, groupByClause, null, 0, 0);

        int i = 0;
    }

As David has explained before, you don't need to use an Object Alias, unless you join to the same table more than once in the same query, which is not the case here. So your code should look like the following:

public static void GetUnreconciledCompletedOrders() {
            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(CompletedOrderFields.CoDescription, 0, "Description");
            fields.DefineField(CompletedOrderFields.CoGross, 1, "Total");
            fields.DefineField(ProductOrderFields.OId, 2, "Owner");

            IRelationCollection relations = new RelationCollection();
            relations.Add(CompletedOrderEntity.Relations.ProductOrderEntityUsingProId);

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[2]);
            groupByClause.Add(fields[0]);

            IPredicateExpression filter = new PredicateExpression();
            filter.Add(CompletedOrderFields.CoInvoiceid == 0);
            
            DataTable summaryList = new DataTable();
            TypedListDAO dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, summaryList, 0, null, filter, relations, false, groupByClause, null, 0, 0);

            int i = 0;
        } 

Also don't forget to add the aggregate part, as I see you do a Group By but you haven't aggrgated the Total row.

Walaa wrote:

As David has explained before, you don't need to use an Object Alias, unless you join to the same table more than once in the same query, which is not the case here. So your code should look like the following:

public static void GetUnreconciledCompletedOrders() {
            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(CompletedOrderFields.CoDescription, 0, "Description");
            fields.DefineField(CompletedOrderFields.CoGross, 1, "Total");
            fields.DefineField(ProductOrderFields.OId, 2, "Owner");

            IRelationCollection relations = new RelationCollection();
            relations.Add(CompletedOrderEntity.Relations.ProductOrderEntityUsingProId);

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[2]);
            groupByClause.Add(fields[0]);

            IPredicateExpression filter = new PredicateExpression();
            filter.Add(CompletedOrderFields.CoInvoiceid == 0);
            
            DataTable summaryList = new DataTable();
            TypedListDAO dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, summaryList, 0, null, filter, relations, false, groupByClause, null, 0, 0);

            int i = 0;
        } 

Also don't forget to add the aggregate part, as I see you do a Group By but you haven't aggrgated the Total row.

Hi Everyone.

Many thanks for your help on this, I now understand the concept of the aliases so fully understand whats happening now. For anyone in future to get this to work correctly I had to add the aggregate function as follows:

fields.DefineField(CompletedOrderFields.CoGross, 1, "Total", AggregateFunction.Sum);

Again 1st class support and many thanks

Rich