Hi,

I'm using a dynamic list with an aggregate function being applied to a field. Is there a way to add a sortclause which uses this field to sort? I can't seem to figure it out. SortClauseFactory.Create only accepts the entity field index, no aliases that I'm using to define that field.

In the code below, I want to sort by the third field, "SumOfEffort". Any help would be appreciated.

Thanks!

                                                ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField( UserFieldIndex.FirstName, 0, "FirstNameUser", "Player");
            fields.DefineField( UserFieldIndex.LastName, 1, "LastNameUser", "Player");
            fields.DefineField( LeagueUserFieldIndex.ScoreEffort, 2, "SumOfEffort", "WorkoutExercise" );

            fields[2].AggregateFunctionToApply = AggregateFunction.Sum;

            IRelationCollection relations = new RelationCollection();
            relations.Add( UserEntity.Relations.LeagueUserEntityUsingUserId, "Player", "LeagueUser", JoinHint.Inner );
            relations.Add( UserEntity.Relations.WorkoutExerciseEntityUsingUserId, "Player", "WorkoutExercise", JoinHint.Left );

            PredicateExpression filter = new PredicateExpression();
            filter.Add(PredicateFactory.CompareValue( LeagueUserFieldIndex.LeagueId, ComparisonOperator.Equal, LeagueId, "LeagueUser" ));
            PredicateExpression dateFilter = new PredicateExpression();

            dateFilter.Add( PredicateFactory.Between( WorkoutExerciseFieldIndex.Date, league.DateStart, league.DateFinish, "WorkoutExercise" ) );
            dateFilter.AddWithOr( PredicateFactory.CompareNull( WorkoutExerciseFieldIndex.Date, "WorkoutExercise" ) );

            filter.AddWithAnd( dateFilter );

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

            ISortExpression sortClauses = new SortExpression( SortClauseFactory.Create( UserFieldIndex.UserId, SortOperator.Descending, "SumOfEffort" ) );
            

Use the SortClause constructor to create a new SortClause instance:

ISortExpression sortClauses = new SortExpression(new SortClause(fields[2], null, SortOperator.Descending));

After all, the SortClauseFactory offers an easy way to create a SortClause instance

Otis wrote:

Use the SortClause constructor to create a new SortClause instance:

ISortExpression sortClauses = new SortExpression(new SortClause(fields[2], null, SortOperator.Descending));

After all, the SortClauseFactory offers an easy way to create a SortClause instance

Ah, so easy! Frans, you rock! Thanks for your help (Again!)

I read this with interest and tried it in a dynamic list that I am building against a firebird databse and I get the firebird error:

"Invalid expression in the ORDER By Clause (not contained in either an aggregate function or the group by clause)"

I have a result set with 5 columns

Columns 0,1,2,3 are included in the group by clause using myGroupByClause.Add

Column 4 is not in the group by but has the AggregateFunction.Sum applied to it

So I cant understand why it is saying there is a problem

many Thanks

P.S. This may be related to the other post I just made... If so I am sorry

here is the full code of the method

public static DataTable MGRGetProductValueTable(DateTime startDate, DateTime endDate, bool isSales, int dealStateID, bool useWeightedValues)
        {
            try
            {
                IDataAccessAdapter myAdapter;
                myAdapter = LLBLAdapter.GetNew();

                ResultsetFields myFields = new ResultsetFields(5);
                myFields.DefineField(CubeDealItemUserFieldIndex.Deleted, 0, "DELETED","CUBE_DEAL_ITEM_USER");
                myFields.DefineField(CubeDealItemUserFieldIndex.DealStateID, 1, "DEAL_STATE_ID","CUBE_DEAL_ITEM_USER");
                myFields.DefineField(CubeDealItemUserFieldIndex.ProductID, 2, "PRODUCT_ID","PRODUCT");
                myFields.DefineField(ProductFieldIndex.Name, 3, "NAME","PRODUCT");

                if (useWeightedValues)
                {
                    if (isSales)
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserWeightedLineSaleValue, 4, "USER_WEIGHTED_LINE_SALE_VALUE","CUBE_DEAL_ITEM_USER",AggregateFunction.Sum);
                    }
                    else
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserWeightedLineProfitValue, 4, "USER_WEIGHTED_LINE_PROFIT_VALUE","CUBE_DEAL_ITEM_USER",AggregateFunction.Sum);
                    }
                }
                else
                {
                    if (isSales)
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserLineSaleValue, 4, "USER_LINE_SALE_VALUE","CUBE_DEAL_ITEM_USER",AggregateFunction.Sum);
                    }
                    else
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserLineProfitValue, 4, "USER_LINE_PROFIT_VALUE","CUBE_DEAL_ITEM_USER",AggregateFunction.Sum);
                    }
                }
                
                IRelationPredicateBucket myBucket = new RelationPredicateBucket();  
                myBucket.Relations.Add(CubeDealItemUserEntity.Relations.ProductEntityUsingProductID);

                IPredicateExpression myExpression = new PredicateExpression();              
                myExpression.Add(PredicateFactory.CompareValue(CubeDealItemUserFieldIndex.CubeDate, ComparisonOperator.LessEqual, endDate));
                myExpression.AddWithAnd(PredicateFactory.CompareValue(CubeDealItemUserFieldIndex.CubeDate, ComparisonOperator.GreaterEqual, startDate));
                myBucket.PredicateExpression.Add(myExpression);

                IGroupByCollection myGroupByClause = new GroupByCollection();
                myGroupByClause.Add(myFields[0]);
                myGroupByClause.Add(myFields[1]);
                myGroupByClause.Add(myFields[2]);
                myGroupByClause.Add(myFields[3]);

                IPredicateExpression myHavingFilter = new PredicateExpression();
                myHavingFilter.Add(PredicateFactory.CompareValue(CubeDealItemUserFieldIndex.DealStateID, ComparisonOperator.Equal, dealStateID));
                myHavingFilter.AddWithAnd(PredicateFactory.CompareValue(CubeDealItemUserFieldIndex.Deleted, ComparisonOperator.Equal, 0));
                myGroupByClause.HavingClause = myHavingFilter;
                
                ISortExpression mySorter = new SortExpression(new SortClause(myFields[4],null,SortOperator.Descending));

                DataTable myDynamicList = new DataTable();
                myAdapter.FetchTypedList(myFields, myDynamicList, myBucket, 0, mySorter, true, myGroupByClause);

                return myDynamicList;

            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return null;
            }

Really weird indeed. I do remember there is some issue with order by and firebird in which you had to specify the number of the column, but I had addressed that with a workaround in code... What's the query that's generated? (please enable tracing to grab the query).

Um eeer how do I enable tracing

hplloyd wrote:

Um eeer how do I enable tracing

Heh You need 1.0.2004.2 for that, but you should then check out 'Troubleshooting and debugging' in the "Using hte generated code" section in the manual .

What an excellent little utility!!

Here is the SQL that is generated - I.e. the SQL that causes a Firebird Error

Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT CUBE_DEAL_ITEM_USER.DELETED AS DELETED,CUBE_DEAL_ITEM_USER.DEAL_STATE_ID AS DEAL_STATE_ID,PRODUCT.PRODUCT_ID AS PRODUCT_ID,PRODUCT.NAME AS NAME,SUM(CUBE_DEAL_ITEM_USER.USER_WEIGHTED_LINE_SALE_VALUE) AS USER_WEIGHTED_LINE_SALE_VALUE FROM ( PRODUCT  INNER JOIN CUBE_DEAL_ITEM_USER  ON  PRODUCT.PRODUCT_ID=CUBE_DEAL_ITEM_USER.PRODUCT_ID) WHERE ( ( CUBE_DEAL_ITEM_USER.CUBE_DATE <= @CubeDate1 And CUBE_DEAL_ITEM_USER.CUBE_DATE >= @CubeDate2)) GROUP BY CUBE_DEAL_ITEM_USER.DELETED,CUBE_DEAL_ITEM_USER.DEAL_STATE_ID,PRODUCT.PRODUCT_ID,PRODUCT.NAME HAVING ( CUBE_DEAL_ITEM_USER.DEAL_STATE_ID = @DealStateID3 And CUBE_DEAL_ITEM_USER.DELETED = @Deleted4) ORDER BY USER_WEIGHTED_LINE_SALE_VALUE DESC
    Parameter: @CubeDate1 : Date. Length: 8. Precision: 0. Scale: 0. Direction: Input. Value: 31/07/2005 09:26:35.
    Parameter: @CubeDate2 : Date. Length: 8. Precision: 0. Scale: 0. Direction: Input. Value: 01/08/2004 09:26:35.
    Parameter: @DealStateID3 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 200.
    Parameter: @Deleted4 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 0.

It looks to me that it could be gettiung confused re the facrt that I have given the aggregated field the same alias as the field name itself...

Now that I know how to fix that (from my other post)... I will ammend and retry

I'll await your retry .

Thanks for you help on both this and my other post - I know they are related...

I have now amended to the code to

public static DataTable MGRGetProductValueTable(DateTime startDate, DateTime endDate, bool isSales, int dealStateID, bool useWeightedValues)
        {
            try
            {
                IDataAccessAdapter myAdapter;
                myAdapter = LLBLAdapter.GetNew();

                ResultsetFields myFields = new ResultsetFields(5);
                myFields.DefineField(CubeDealItemUserFieldIndex.Deleted, 0, "Deleted","CUBE_DEAL_ITEM_USER");
                myFields.DefineField(CubeDealItemUserFieldIndex.DealStateID, 1, "DealStateID","CUBE_DEAL_ITEM_USER");
                myFields.DefineField(CubeDealItemUserFieldIndex.ProductID, 2, "ProductID","PRODUCT");
                myFields.DefineField(ProductFieldIndex.Name, 3, "ProductName","PRODUCT");

                if (useWeightedValues)
                {
                    if (isSales)
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserWeightedLineSaleValue, 4, "Value","CUBE_DEAL_ITEM_USER",AggregateFunction.Sum);
                    }
                    else
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserWeightedLineProfitValue, 4, "Value","CUBE_DEAL_ITEM_USER",AggregateFunction.Sum);
                    }
                }
                else
                {
                    if (isSales)
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserLineSaleValue, 4, "Value","CUBE_DEAL_ITEM_USER",AggregateFunction.Sum);
                    }
                    else
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserLineProfitValue, 4, "Value","CUBE_DEAL_ITEM_USER",AggregateFunction.Sum);
                    }
                }
                
                IRelationPredicateBucket myBucket = new RelationPredicateBucket();  
                myBucket.Relations.Add(CubeDealItemUserEntity.Relations.ProductEntityUsingProductID);

                IPredicateExpression myExpression = new PredicateExpression();              
                myExpression.Add(PredicateFactory.CompareValue(CubeDealItemUserFieldIndex.CubeDate, ComparisonOperator.LessEqual, endDate));
                myExpression.AddWithAnd(PredicateFactory.CompareValue(CubeDealItemUserFieldIndex.CubeDate, ComparisonOperator.GreaterEqual, startDate));
                myBucket.PredicateExpression.Add(myExpression);

                IGroupByCollection myGroupByClause = new GroupByCollection();
                myGroupByClause.Add(myFields[0]);
                myGroupByClause.Add(myFields[1]);
                myGroupByClause.Add(myFields[2]);
                myGroupByClause.Add(myFields[3]);

                IPredicateExpression myHavingFilter = new PredicateExpression();
                myHavingFilter.Add(new FieldCompareValuePredicate(myFields[1],null,ComparisonOperator.Equal,dealStateID));
                myHavingFilter.AddWithAnd(new FieldCompareValuePredicate(myFields[0],null,ComparisonOperator.Equal,0));
                myGroupByClause.HavingClause = myHavingFilter;
                
                ISortExpression mySorter = new SortExpression(new SortClause(myFields[4],null,SortOperator.Descending));

                DataTable myDynamicList = new DataTable();
                myAdapter.FetchTypedList(myFields, myDynamicList, myBucket, 0, mySorter, true, myGroupByClause);

                return myDynamicList;

            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return null;
            }

        }

Whiuch generates the SQL:

Generated Sql query: 
    Query: SELECT CUBE_DEAL_ITEM_USER.DELETED AS Deleted,CUBE_DEAL_ITEM_USER.DEAL_STATE_ID AS DealStateID,PRODUCT.PRODUCT_ID AS ProductID,PRODUCT.NAME AS ProductName,SUM(CUBE_DEAL_ITEM_USER.USER_WEIGHTED_LINE_SALE_VALUE) AS Value FROM ( PRODUCT  INNER JOIN CUBE_DEAL_ITEM_USER  ON  PRODUCT.PRODUCT_ID=CUBE_DEAL_ITEM_USER.PRODUCT_ID) WHERE ( ( CUBE_DEAL_ITEM_USER.CUBE_DATE <= @CubeDate1 And CUBE_DEAL_ITEM_USER.CUBE_DATE >= @CubeDate2)) GROUP BY CUBE_DEAL_ITEM_USER.DELETED,CUBE_DEAL_ITEM_USER.DEAL_STATE_ID,PRODUCT.PRODUCT_ID,PRODUCT.NAME HAVING ( CUBE_DEAL_ITEM_USER.DEAL_STATE_ID = @DealStateID3 And CUBE_DEAL_ITEM_USER.DELETED = @Deleted4) ORDER BY Value DESC
    Parameter: @CubeDate1 : Date. Length: 8. Precision: 0. Scale: 0. Direction: Input. Value: 31/07/2005 10:12:08.
    Parameter: @CubeDate2 : Date. Length: 8. Precision: 0. Scale: 0. Direction: Input. Value: 01/08/2004 10:12:08.
    Parameter: @DealStateID3 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 200.
    Parameter: @Deleted4 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 0.

Which looks spot on to me...

However I get the Firebird Error as per my other post:

"Dynamic SQL Error.... Token unknown... Line 1 Char 211 Value"

I.E. I dont thisnk it likes the fact that I have called the Order by column "Value" - it must be a reserved word... so I change the column alias to "myVal" and I get a different error

"Dynamic SQL Error.... Column unknown... myVal"

Now I am confused... I hope this is all clear to you!!

Otis,

I have pasted the SQL into IBExpert and have been trying to get it to work.

What works in IB Expert is to replace

ORDER BY myVal DESC

with

ORDER BY SUM(CUBE_DEAL_ITEM_USER.USER_WEIGHTED_LINE_SALE_VALUE) DESC

I.e. when building the SQL behind the ISortExpression, the myFields[4] needs to pick up "SUM(CUBE_DEAL_ITEM_USER.USER_WEIGHTED_LINE_SALE_VALUE) " not "myVal"

Does this help?

hplloyd wrote:

Otis,

I have pasted the SQL into IBExpert and have been trying to get it to work.

What works in IB Expert is to replace

ORDER BY myVal DESC

with

ORDER BY SUM(CUBE_DEAL_ITEM_USER.USER_WEIGHTED_LINE_SALE_VALUE) DESC

I.e. when building the SQL behind the ISortExpression, the myFields[4] needs to pick up "SUM(CUBE_DEAL_ITEM_USER.USER_WEIGHTED_LINE_SALE_VALUE) " not "myVal" Does this help?

Hmm. So column aliasses aren't allowed in the order by.

It's then a bug in the DQE. I've to fix this.

Fixed in next build.

EXCELLENT

I shall use the code without the sort until the next fix is released....

Many thanks

Fix is now available.

It works perfectly now

Many thanks

message removed, Sorry