Search code examples
sqlintersystems-cache

Intersystems Cache: An aggregate function cannot be used in a WHERE or GROUP BY clause

I am learning Intersystems Cache and have the following Function which I based off code running an earlier version of the product (I am 17.2).

Query dueToday(rToday As DType.Date) As %SQLQuery(ROWSPEC = "Book:User.Books,RefNo:%Integer")
{
    SELECT BookODue.Rent, BookODue.RefNo
      FROM BookODue
      LEFT JOIN BookTran
      ON BookODue.Rent = BookTran.Rent
      WHERE (BookODue.AuditDate = :rToday) AND (MAX(BookTran.TranDate) < :rToday)
}

When I compile this I get

An aggregate function cannot be used in a WHERE or GROUP BY clause

Any help appreciated.

Gracias

Solution

  • I could speculate that you really want:

    SELECT bo.Rent, bo.RefNo
FROM BookODue bo JOIN
     BookTran bt
     ON bo.Rent = bt.Rent
WHERE bo.AuditDate = :rToday
GROUP BY bo.Rent, bo.RefNo
HAVING MAX(bt.TranDate) < :rToday;