@Test
public void DocoExamples912() throws Exception {
query().from(ord, cust)
.join(ord.lineItems, item).join(item.product, product)
.from(catalog).join(catalog.prices, price).where(
ord.paid.not().and(ord.customer.eq(cust)).and(
price.product.eq(product)).and(
catalog.effectiveDate.gt(DateExpression.currentDate())).and(
catalog.effectiveDate.gtAny(
sub().from(catalog).where(
catalog.effectiveDate.lt(DateExpression.currentDate()))
.list(catalog.effectiveDate))))
.groupBy(ord).having(price.amount.sum().gt(0l))
.orderBy(price.amount.sum().desc())
.select(ord.id, price.amount.sum(), item.count());
Customer c1 = new Customer();
Catalog c2 = new Catalog();
query().from(ord)
.join(ord.lineItems, item).join(item.product, product)
.from(catalog).join(catalog.prices, price).where(
ord.paid.not().and(ord.customer.eq(c1)).and(
price.product.eq(product)).and(catalog.eq(c2)))
.groupBy(ord).having(price.amount.sum().gt(0l))
.orderBy(price.amount.sum().desc())
.select(ord.id, price.amount.sum(), item.count());
}