I am building a dynamic list with an aggregate function on one of the fields.

When defining the fields I add individual lines of code for each column

e.g.

myFields.DefineField(....

however I get a run time error (from my firebird database) when the "alias" I provide for the field in the DefineField expression does not exactly match the field name in the database.

E.g. I have a field called CUBE_MONTH that I have mapped to a property called "CubeMonth" in LLBL.

This will work

myFields.DefineField(CubeFieldIndex.CubeMonth,0,"CUBE_MONTH","Cube")

however this will crash at run time with a firebird error saying the the field "WhateverIWant" does not exist.

myFields.DefineField(CubeFieldIndex.CubeMonth,0,"WhateverIWant","Cube")

I want to be able to use whatever alias I like... is this a bug?

Many thanks

P.S. The crash happens when the call to adapter.FetchTypedList is made

PPS I have also found that the table alias must also exactly match the table name in the database....I guess this is part of teh same issue...

Please paste your complete code of the routine which builds the dyn. list and executes it.

here you are... I hope it makes sense

public static double MGRGetSumValue(DateTime monthStartDate, bool isSales, int dealStateID, bool useWeightedValues)
        {
            try
            {
                IDataAccessAdapter myAdapter;
                myAdapter = LLBLAdapter.GetNew();

                ResultsetFields myFields = new ResultsetFields(4);
                myFields.DefineField(CubeDealItemUserFieldIndex.CubeMonth, 0, "CUBE_MONTH","Cube");
                myFields.DefineField(CubeDealItemUserFieldIndex.Deleted, 1, "DELETED","Cube");
                myFields.DefineField(CubeDealItemUserFieldIndex.DealStateID, 2, "DEAL_STATE_ID","Cube");

                if (useWeightedValues)
                {
                    if (isSales)
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserWeightedLineSaleValue, 3, "USER_WEIGHTED_LINE_SALE_VALUE","Cube",AggregateFunction.Sum);
                    }
                    else
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserWeightedLineProfitValue, 3, "USER_WEIGHTED_LINE_PROFIT_VALUE","Cube",AggregateFunction.Sum);
                    }
                }
                else
                {
                    if (isSales)
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserLineSaleValue, 3, "USER_LINE_SALE_VALUE","Cube",AggregateFunction.Sum);
                    }
                    else
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserLineProfitValue, 3, "USER_LINE_PROFIT_VALUE","Cube",AggregateFunction.Sum);
                    }
                }
                
                IRelationPredicateBucket myBucket = new RelationPredicateBucket();  

                IGroupByCollection myGroupByClause = new GroupByCollection();
                myGroupByClause.Add(myFields[0]);
                myGroupByClause.Add(myFields[1]);
                myGroupByClause.Add(myFields[2]);
                
                IPredicateExpression myHavingFilter = new PredicateExpression();
                myHavingFilter.Add(PredicateFactory.CompareValue(CubeDealItemUserFieldIndex.CubeMonth, ComparisonOperator.Equal, monthStartDate));
                myHavingFilter.AddWithAnd(PredicateFactory.CompareValue(CubeDealItemUserFieldIndex.DealStateID, ComparisonOperator.Equal, dealStateID));
                myHavingFilter.AddWithAnd(PredicateFactory.CompareValue(CubeDealItemUserFieldIndex.Deleted, ComparisonOperator.Equal, 0));
                myGroupByClause.HavingClause = myHavingFilter;
                
                DataTable myDynamicList = new DataTable();
                myAdapter.FetchTypedList(myFields, myDynamicList, myBucket, 0, null, true, myGroupByClause);


                if (useWeightedValues)
                {
                    if (isSales)
                    {
                        return (double) myDynamicList.Rows[0]["USER_WEIGHTED_LINE_SALE_VALUE"];
                    }
                    else
                    {
                        return (double) myDynamicList.Rows[0]["USER_WEIGHTED_LINE_PROFIT_VALUE"];
                    }
                }
                else
                {
                    if (isSales)
                    {
                        return (double) myDynamicList.Rows[0]["USER_LINE_SALE_VALUE"];
                    }
                    else
                    {
                        return (double) myDynamicList.Rows[0]["USER_LINE_PROFIT_VALUE"];
                    }
                }
            }
            catch
            {
                return 0;
            }
        }

The stuff in capitals are the real field names....this code works however I dont want to have to use the real field names

Thanks

The error comes from the fact you're filtering on the same columns you're aliassing.

You don't have to specify 'Cube' as objectalias, though.

So instead of using the predicatefactory, you should use the FieldCompareValuepredicate constructor and pass in the Fields[index] object, so:

instead of:

myFields.DefineField(CubeDealItemUserFieldIndex.CubeMonth, 0, "CUBE_MONTH","Cube");
//...
myHavingFilter.Add(PredicateFactory.CompareValue(CubeDealItemUserFieldIndex.CubeMonth, ComparisonOperator.Equal, monthStartDate));

do:

myFields.DefineField(CubeDealItemUserFieldIndex.CubeMonth, 0, "CubeMonth");
//...
myHavingFilter.Add(new FieldCompareValuePredicate(myFields[0], null, ComparisonOperator.Equal, monthStartDate));

etc. The reason is that the field in the select list is aliased to 'CubeMonth', but the field in the predicate uses 'CUBE_MONTH'. At least that's what I assume is going on.

Could you paste the SQL that's being generated?

SQL is here

    Query: SELECT Cube.CUBE_MONTH AS CUBE_MONTH,Cube.DELETED AS DELETED,Cube.DEAL_STATE_ID AS DEAL_STATE_ID,SUM(Cube.USER_LINE_SALE_VALUE) AS USER_LINE_SALE_VALUE FROM CUBE_DEAL_ITEM_USER Cube GROUP BY Cube.CUBE_MONTH,Cube.DELETED,Cube.DEAL_STATE_ID HAVING ( CUBE_DEAL_ITEM_USER.CUBE_MONTH = @CubeMonth1 And CUBE_DEAL_ITEM_USER.DEAL_STATE_ID = @DealStateID2 And CUBE_DEAL_ITEM_USER.DELETED = @Deleted3)
    Parameter: @CubeMonth1 : Date. Length: 8. Precision: 0. Scale: 0. Direction: Input. Value: 01/08/2004 09:23:36.
    Parameter: @DealStateID2 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 200.
    Parameter: @Deleted3 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 0.

I will make changes as you request and see if the SQL looks how I want it to look

... A short time later.....

I have now amended the code to:

public static double MGRGetSumValue(DateTime monthStartDate, bool isSales, int dealStateID, bool useWeightedValues)
        {
            try
            {
                IDataAccessAdapter myAdapter;
                myAdapter = LLBLAdapter.GetNew();

                ResultsetFields myFields = new ResultsetFields(4);
                myFields.DefineField(CubeDealItemUserFieldIndex.CubeMonth, 0, "Month");
                myFields.DefineField(CubeDealItemUserFieldIndex.Deleted, 1, "Deleted");
                myFields.DefineField(CubeDealItemUserFieldIndex.DealStateID, 2, "DealStateID");

                if (useWeightedValues)
                {
                    if (isSales)
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserWeightedLineSaleValue, 3, "Value","Cube",AggregateFunction.Sum);
                    }
                    else
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserWeightedLineProfitValue, 3, "Value","Cube",AggregateFunction.Sum);
                    }
                }
                else
                {
                    if (isSales)
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserLineSaleValue, 3, "Value","Cube",AggregateFunction.Sum);
                    }
                    else
                    {
                        myFields.DefineField(CubeDealItemUserFieldIndex.UserLineProfitValue, 3, "Value","Cube",AggregateFunction.Sum);
                    }
                }
                
                IRelationPredicateBucket myBucket = new RelationPredicateBucket();  

                IGroupByCollection myGroupByClause = new GroupByCollection();
                myGroupByClause.Add(myFields[0]);
                myGroupByClause.Add(myFields[1]);
                myGroupByClause.Add(myFields[2]);
                
                IPredicateExpression myHavingFilter = new PredicateExpression();
                myHavingFilter.Add(new FieldCompareValuePredicate(myFields[0],null,ComparisonOperator.Equal,monthStartDate));
                myHavingFilter.AddWithAnd(new FieldCompareValuePredicate(myFields[1],null,ComparisonOperator.Equal,0));
                myHavingFilter.AddWithAnd(new FieldCompareValuePredicate(myFields[2],null,ComparisonOperator.Equal,dealStateID));
                myGroupByClause.HavingClause = myHavingFilter;
                
                DataTable myDynamicList = new DataTable();
                myAdapter.FetchTypedList(myFields, myDynamicList, myBucket, 0, null, true, myGroupByClause);


                return (double) myDynamicList.Rows[0]["Value"];
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show (ex.Message);
                return 0;
            }
        }

Which is much more how I want it (although I would prefer not to have to put an object alias against the agregated field - when I use null, no SQL is generated at all)

Unfortunately I still get the same Firebird error

"Dynamic SQL Error, SQL Error =-104 Token Unknown - line 1 Char 42 Month"

The SQL generated is

Generated Sql query: 
    Query: SELECT CUBE_DEAL_ITEM_USER.CUBE_MONTH AS Month,CUBE_DEAL_ITEM_USER.DELETED AS Deleted,CUBE_DEAL_ITEM_USER.DEAL_STATE_ID AS DealStateID,SUM(Cube.USER_LINE_SALE_VALUE) AS Value FROM CUBE_DEAL_ITEM_USER GROUP BY CUBE_DEAL_ITEM_USER.CUBE_MONTH,CUBE_DEAL_ITEM_USER.DELETED,CUBE_DEAL_ITEM_USER.DEAL_STATE_ID HAVING ( CUBE_DEAL_ITEM_USER.CUBE_MONTH = @Month1 And CUBE_DEAL_ITEM_USER.DELETED = @Deleted2 And CUBE_DEAL_ITEM_USER.DEAL_STATE_ID = @DealStateID3)
    Parameter: @Month1 : Date. Length: 8. Precision: 0. Scale: 0. Direction: Input. Value: 01/08/2004 09:44:55.
    Parameter: @Deleted2 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 0.
    Parameter: @DealStateID3 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 200.

which to me looks absolutely correct

Any ideas?

OK I have cracked this one....

Problems are that

1. The object alias in the aggregate field MUST be the table name
2. "Month" is a reserved word in Fireburd so I have called this alias "Mth"
3. "Value" is a reserved word in Firebird so I have called this alias "myVal"

It all works beautifully now )

Still having problems with the other post that is more complicated with a where clause and order by clause

ok

Yes that other one is a bug in the DQE. I'm fixing that one now.