Is it possible to generate the following kind of query:

SELECT JobId
FROM Job
WHERE   (SELECT SUM(Billing.Amount) FROM Billing WHERE Job.JobId = Billing.JobId) > 0
        AND
        (SELECT COUNT(Payment.PaymentId) FROM Payment WHERE Job.JobId = Payment.JobId) = 0

or

SELECT JobId
FROM Job
WHERE   (SELECT SUM(Billing.Amount) FROM Billing WHERE Job.JobId = Billing.JobId) > 
        (SELECT SUM(Payment.Amount) FROM Payment WHERE Job.JobId = Payment.JobId)

in LLBLGen? I am using version 2 (March build I think). I wanted to use this in a SELECT * FROM Table WHERE JobId IN (...) and stuff the above queries into the IN part. Let me know!

Sorry, I accidentally clicked this as done. I am still wondering if it is possible.

Seth wrote:

Is it possible to generate the following kind of query:

SELECT JobId
FROM Job
WHERE   (SELECT SUM(Billing.Amount) FROM Billing WHERE Job.JobId = Billing.JobId) > 0
        AND
        (SELECT COUNT(Payment.PaymentId) FROM Payment WHERE Job.JobId = Payment.JobId) = 0

I think your SQL could be rewritten for less complexity:

SELECT 
    Job.JobId

FROM 
    Job
    inner join Billing on Job.JobId = Billing.JobId
    inner join Payment on Job.JobId = Payment.JobId

GROUP BY 
    Job.JobId

HAVING 
    SUM(Billing.Amount) > 0
    and COUNT(Payment.PaymentId) = 0

Then, you can use _FieldCompareSetPredicate _in your collection filter. Look at this simple example:

Statement: I want all orders related to customers that have more than 20 orders.

Possible SQL:

select * from orders
where customerId in
(
    SELECT c.customerId 
    FROM customers c
        inner join orders o on c.customerId = o.customerid
    GROUP BY c.customerId
    HAVING count(o.orderid) > 20
)

** Possible LLBLGen code (2.0, Adapter)**

// sub-set relations
IRelationCollection subSetRelations = new RelationCollection();
subSetRelations.Add(CustomersEntity.Relations.OrdersEntityUsingCustomerId);

// sub-set groupBy section
IGroupByCollection subSetGroupByClause = new GroupByCollection();
subSetGroupByClause.Add(CustomersFields.CustomerId);
subSetGroupByClause.HavingClause = new PredicateExpression(OrdersFields.OrderId.SetAggregateFunction(AggregateFunction.Count) > 20);

// collection to fetch
EntityCollection<OrdersEntity> orders = new EntityCollection<OrdersEntity>(new OrdersEntityFactory());

// collection filter
IRelationPredicateBucket ordersFilter = new RelationPredicateBucket();
ordersFilter.PredicateExpression.Add(
    new FieldCompareSetPredicate(
        OrdersFields.CustomerId, null,
        CustomersFields.CustomerId, null,
        SetOperator.In,
        null,
        bucket.Relations,"", 0, null, false,
        subSetGroupByClause));


// fetch results            
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, ordersFilter);
}

// show results
foreach (OrdersEntity order in orders )
{
    Console.WriteLine(order.OrderId);
}

So in your case you need to add two FieldCompareSetPredicate's to the collection filter

Hope helpful

Thanks for the reply. That kind of query did not work however. When there are unequal numbers of Payment and Billing fields, it does not aggregate correctly. The sums are off because when it does the joins it attempts to have the same amount of rows for billing AND payments and thus the values come out to be SUM(Billing.Amount) * COUNT(Payment.PaymentId) and SUM(Payment.Amount) * COUNT(Billing.BillingId). I have already set it up as you have described. Here is the relation hierarchy:

               Job
              /  \
      Billing   Payment

where Job -> 1:m Billing and Job -> 1:m Payment. The query I need is to have SUM(Billing.Amount) > SUM(Payment.Amount). I set it up like you said but the calculations were wrong (I checked the generated SQL and ran it against the datasource. Unfortunately, I cannot use sp's because the software targets multiple providers. Let me know, and thanks again.

Hello,

you can defined directly the request you have in llbl. You can see sample in "Generated code - Field expressions and aggregates -->Expressions in predicates"

It will allow you to create aggregate(SUM) as an expression so you can apply it in your WHERE clause. What version of llbl do you use and what scenario(adapter/self service)?

I use v2 and adapter scenario. I did do the aggregates as you described, but the calculations are wrong. Thanks for your help!

I am wondering if the SQL statement I made above can be generated from LLBLGen? Let me know. I truly appreciate your help!

Seth wrote:

Is it possible to generate the following kind of query:

SELECT JobId
FROM Job
WHERE   (SELECT SUM(Billing.Amount) FROM Billing WHERE Job.JobId = Billing.JobId) > 0
        AND
        (SELECT COUNT(Payment.PaymentId) FROM Payment WHERE Job.JobId = Payment.JobId) = 0

or

SELECT JobId
FROM Job
WHERE   (SELECT SUM(Billing.Amount) FROM Billing WHERE Job.JobId = Billing.JobId) > 
        (SELECT SUM(Payment.Amount) FROM Payment WHERE Job.JobId = Payment.JobId)

in LLBLGen? I am using version 2 (March build I think). I wanted to use this in a SELECT * FROM Table WHERE JobId IN (...) and stuff the above queries into the IN part. Let me know!

Yes it can be done by LLBLGen Pro.

Use a DynamicList to select one field.

Use a FieldCompareValuePredicate for each of the 2 predicates (Field1 > 0) & (Field2 = 0)

Before that you should do the following: Field1.SetExpression(/* use a Scalar query expressions here/) Field2.SetExpression(/ use a Scalar query expressions here*/)

Please check the manual: "Using the generated code -> Field expressions and aggregates"

- Expressions in predicates - Scalar query expressions

If anything went wrong, then please a code snippet of what you have been trying.