I am trying to figure out how to do the following in LLBLGen. Specifically, how do I do filtering on both WHERE and HAVING, and how do I do the grouping. I've read the documentation but I'm not getting it.

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

Hi hope this helps. Here is the example code for the inner select

   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

            ' WHERE o.OrderDate Between '1997-01-01' AND '1997-3-31'
            Dim filter As IPredicateExpression = New PredicateExpression
            filter.Add(New FieldBetweenPredicate(EntityFieldFactory.Create(OrdersFieldIndex.OrderDate), "1997-01-01", "1997-03-31"))

            ' GROUP BY o.CustomerID
            Dim fields As New ResultsetFields(2)
            fields.DefineField(OrdersFieldIndex.CustomerID, 0, "CustomerID", "Orders")
            fields.DefineField(OrdersFieldIndex.CustomerID, 1, "CustomerID", "Orders")
            Dim groupByClause As IGroupByCollection = New GroupByCollection
            groupByClause.Add(fields(0))

            ' Expression for Sum(UnitPrice * Quantity * (1 - Discount)) As TotalOrders
            Dim productPriceExpression As IExpression = New Expression(OrderDetailsFields.UnitPrice, ExOp.Mul, OrderDetailsFields.Quantity)
            Dim discountExpression As IExpression = New Expression(1.0F, ExOp.Sub, OrderDetailsFields.Discount)
            Dim totalPriceExpression As IExpression = New Expression(productPriceExpression, ExOp.Mul, discountExpression)
            fields(1).ExpressionToApply = totalPriceExpression
            fields(1).AggregateFunctionToApply = AggregateFunction.Sum

            'INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
            Dim relations As New RelationCollection
            relations.Add(OrdersEntity.Relations.OrderDetailsEntityUsingOrderID, JoinHint.Inner)

            ' HAVING Sum(UnitPrice * Quantity * (1 - Discount)) > 1000
            Dim havingFilter As IPredicateExpression = New PredicateExpression
            havingFilter.Add(New FieldCompareValuePredicate(fields(1), Nothing, ComparisonOperator.GreaterThan, 1000.0F))
            groupByClause.HavingClause = havingFilter

            Dim dao As New TypedListDAO
            Dim tlist As New DataTable
            dao.GetMultiAsDataTable(fields, tlist, 0, Nothing, filter, relations, True, groupByClause, Nothing, 0, 0)