With the error message: “could not resolve property: MONTH(CreationDate) of: EmailHistory”
The cfquery alternative is:
SELECT COUNT(EmailHistoryID) AS EmailsSent, MONTH(CreationDate) AS TheMonth, YEAR(CreationDate) AS TheYear
FROM tbl_EmailHistory
GROUP BY MONTH(CreationDate), YEAR(CreationDate)
ORDER BY TheYear, TheMonth
…but I would like to use the CBORM method if possible!
....but Hibernate does not like applying the SQL functions Month and Year
to a property. Specifically the groupproperty doesn't like either the alias
("...could not resolve property: TheMonth of: EmailHistory...") or the SQL
functions ("...could not resolve property: MONTH(CreationDate) of:
EmailHistory...").
I've also tried:
prc.SentSummary = EmailHistoryService.executeQuery(query= "SELECT
COUNT(EmailHistoryID) AS EmailsSent, MONTH(eh.CreationDate) AS TheMonth,
YEAR(eh.CreationDate) AS TheYear from EmailHistory eh GROUP BY
MONTH(eh.CreationDate), YEAR(eh.CreationDate)", asQuery = true );
This results in:
Can't cast Object type [[Ljava.lang.Object;] to a value of type [Component]
BUT the log file shows a correct SQL statement:
select
count(emailhisto0_.EmailHistoryID) as col_0_0_,
month(emailhisto0_.CreationDate) as col_1_0_,
year(emailhisto0_.CreationDate) as col_2_0_
from
tbl_EmailHistory emailhisto0_
group by
month(emailhisto0_.CreationDate) ,
year(emailhisto0_.CreationDate)