hi,

i have a question about constructing a group by query in llblgen (we're using version 2.5).

i would like to write the following query: select v.id, v.name, max(v.orders) from view_example v where v.number = [number] and v.date between [startDate] and [endDate] and v.anothernumber in ([2],[3]) group by v.id, v.name

how can i accomplish this? i tried something with resultfields and group by collection but then i didn't know where to leave the filter or the from clause.

we're using the selfservice template.

any help appreciated.

t.i.a., ratjetoes.

Hi

Please can you post what you have attempted so far - so we can see where you were heading

Thanks

Matt

hi,

this is wat i've got so far: VTest is the name of the view.

// Define fields
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(VTest.Customer, 0);
fields.DefineField(VTest.Employee, 1);
fields.DefineField(VTest.JustANumber, 2, AggregateFunction.Max);

// Define filter
PredicateExpression exp = new PredicateExpression(VTest.JustAnId == justAnId);
exp.Add(new FieldBetweenPredicate(VTest.JustADate, start, end));
PredicateExpression expIn = new PredicateExpression();
foreach (int order in orders)
{
        expIn.AddWithOr(order);
}
exp.Add(expIn);

// Group by
GroupByCollection group = new GroupByCollection(fields[0]);
group.Add(fields[1]);

// Sort
SortExpression sort = new SortExpression(VTest.Customer | SortOperator.Ascending);
sort.Add(VTest.Employee | SortOperator.Ascending);

// Retrieve data
DataTable dt = null;
using (DataAccessAdapterBase adpt = new DataAccessAdapterBase())
{
    adpt.FetchTypedList(fields, dt, null, 0, sort, false, group);
}

The problem is that I can't find a DataAccessAdapter class, only the base class (which you can't instantiate). How can i create an instance of the DataAccessAdapter (which dll needs referencing)? Maybe it has something to do with us using SelfService pattern? Also, since I'm just selecting from a view, how do I construct the FROM clause of the sqlStmt (i.e. FROM VTest)? I found an example which uses RelationPredicateBucket but I have just one view. Does LLBLGen detects this for you (from the fields which you enter in the select clause = fieldresultset)?

T.i.a., ratjetoes.

The problem is that I can't find a DataAccessAdapter class, only the base class (which you can't instantiate). How can i create an instance of the DataAccessAdapter (which dll needs referencing)? Maybe it has something to do with us using SelfService pattern?

Exactly, DataAccessAdapter is not available in SelfServicing. Instead you should use:

TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable();

GetMultiAsDaaTable() also accepts a predicate expression to passthe filter. Please check the following docs link

Also, since I'm just selecting from a view, how do I construct the FROM clause of the sqlStmt (i.e. FROM VTest)? I found an example which uses RelationPredicateBucket but I have just one view. Does LLBLGen detects this for you (from the fields which you enter in the select clause = fieldresultset)?

LLBLGen detects that for you.

Tnx, works like a charm!