Otis:

I second this request. It is the one thing that keeps me using stored procedures for some things at this point. I would say that it is the need to do aggregates in the sub-select that is the real reason for doing the sub-select. Most other things can be accomplished with a join.

Given the sort of sub-select below, I don't know any way to get this currently with LLBLGen and I often need to get this kind of information for reporting purposes.

SELECT c.CustomerID , c.CompanyName , t1.TotalOrders

FROM Customers c INNER JOIN ( Select CustomerID , Sum(UnitPrice * Quantity * (1 - Discount)) As TotalOrders

FROM 
    Orders o
    INNER JOIN [Order Details] od
    ON o.OrderID = od.OrderID
WHERE 
    o.OrderDate Between '1997-01-01' AND '1997-3-31'

GROUP BY 
    o.CustomerID

HAVING 
    Sum(UnitPrice * Quantity * (1 - Discount)) > 1000

) t1

ON c.CustomerID = t1.CustomerID

Joel Reinford Data Management Solutions LLC

P.S. Any news on the beta timeframe?

Isn't that also rewritable as a normal join query or with a view?

The thing is, how would that derived table be specified? I only see it happening if an object gets a fieldslist, relationcollection, predicateexpression, sortexpression and a groupbycollection, and is used a part of an entityrelation, but that's not logical.

Maybe I should make my query a bit more specific. The thing I get hung up on is the ability to both aggregate and specifiy criteria on the sub-select at run-time.

The SQL would look something like this:

SELECT c.CustomerID , c.CompanyName , t1.TotalOrders

FROM Customers c INNER JOIN ( Select CustomerID , Sum(UnitPrice * Quantity * (1 - Discount)) As TotalOrders

FROM Orders o INNER JOIN [Order Details] od ON o.OrderID = od.OrderID WHERE o.OrderDate Between @OrderDate1 AND @OrderDate2 --'1997-01-01' AND '1997-3-31'

GROUP BY o.CustomerID

HAVING Sum(UnitPrice * Quantity * (1 - Discount)) > @OrderTotal1 -- 1000

) t1

ON c.CustomerID = t1.CustomerID

Given all the amazing things that the DQE can do already, it seems like there must be a way to do sub selects with aggregates. Maybe we need something like a TypedListWithParameters. Your answer to that might be that I should just use stored procedures for this situation and be done with it. I'm working with SQL Server so I would describe what I'm looking for as similar to view that has been joined to a table-valued UDF.

Joel Reinford Data Management Solutions LLC

You can do aggregates/groupby in a fieldcompareset predicate, which gives you a subquery, though not one you can use in a FROM clause, but in a WHERE clause (although you can specify it as a CustomFilter on a relation so it ends up in the ON clause of a join)