I'm having a hard time trying to convert this query using adapter on a dynamic list. I just don't know how to use one of the ResultFields to be a placeholder for the expression and aggregate functions.
Below is the query i'm trying to use:
SELECT Format.Description AS Format, Sum(Song.Stock) AS Quantity, Sum([Song].[Stock]*[Song].[Price]) AS Valuation
FROM Format INNER JOIN Song ON Format.Format = Song.Format
GROUP BY Format.Description
HAVING (((Sum(Song.Stock))<>0))
ORDER BY Sum([Song].[Stock]*[Song].[Price]) DESC;
Below is the code I have written without the quantity field but it fails on the FetchTypedList... I'm pretty sure I have cocked this up but don't know how to correct it.
ResultsetFields fields = new ResultsetFields(2);
IExpression stockPriceExpression = new Expression(
SongFields.Price, ExOp.Mul, SongFields.Stock);
fields.DefineField(FormatFieldIndex.Description, 0, "Format");
fields[1] = EntityFieldsFactory.CreateEntityFieldsObject(EntityType.SongEntity)["Price"];
fields[1].ExpressionToApply = stockPriceExpression;
fields[1].AggregateFunctionToApply = AggregateFunction.Sum;
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
IPredicateExpression havingFilter = new PredicateExpression();
havingFilter.Add(new FieldCompareValuePredicate(fields[1], null, ComparisonOperator.GreaterThan, 0.0M));
groupByClause.HavingClause = havingFilter;
ISortExpression sorter = new SortExpression();
sorter.Add(SortClauseFactory.Create(FormatFieldIndex.Description, SortOperator.Descending));
DataTable dt = new DataTable();
DataSet ds = new DataSet();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchTypedList(fields, dt, null, 0, sorter, false, groupByClause, 0, 0);
}
ds.Tables.Add(dt);
return ds;
Any help would be appreciated and thanks in advance.
Geoff.
