Is it possible to do the following as a scalar query:

SELECT
     SUM(Amount) - SUM(Other_Amount) NetAmount
FROM 
     SomeTable

I've read up on both aggregates and expressions, but I can't seem to put them together into an IEntityField2, which is what the GetScalar method needs.

Version 2, latest builds, Adapter vs. SQL Server.

Thanks,

Phil

How about something like this.

                IEntityField2 amount = SomeTableFields.Amount.SetAggregateFunction(AggregateFunction.Sum);
                IEntityField2 otherAmount = SomeTableFields.OtherAmount.SetAggregateFunction(AggregateFunction.Sum);
                IEntityField2 netAmount = SomeTableFields.Amount.SetFieldAlias("NetAmount");
                netAmount.ExpressionToApply = new Expression(amount, ExOp.Sub, otherAmount);

                using (DataAccessAdapter da = new DataAccessAdapter())
                {
                    ResultsetFields fields = new ResultsetFields(1);
                    fields.DefineField(netAmount, 0);
                    DataTable dynamicList = new DataTable();
                    adapter.FetchTypedList(fields, dynamicList, null);
                }

I forgot the scalar part, let me take a look at that real quick.

ok, try this instead.

                IEntityField2 amount = SomeTableFields.Amount.SetAggregateFunction(AggregateFunction.Sum);
                IEntityField2 otherAmount = SomeTableFields.OtherAmount.SetAggregateFunction(AggregateFunction.Sum);
                IEntityField2 netAmount = SomeTableFields.Amount.SetFieldAlias("NetAmount");
                netAmount.ExpressionToApply = new Expression(amount, ExOp.Sub, otherAmount);

                using (DataAccessAdapter da = new DataAccessAdapter())
                {
                    ResultsetFields fields = new ResultsetFields(1);
                    fields.DefineField(netAmount, 0);
                    int total = (int)adapter.GetScalar(fields, null, null);
                }

Finally got back around to trying this today. It worked, thanks!

Phil