0

I have a query like :

select name, trunc( date, 'MM' ), sum( number )
from t$mytable
group by name, trunc( date, 'MM' );

which runs perfectly, and gives expected result. I implemented exactly the same query in JPA criteria API :

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery( pClass );
Metamodel metamodel = em.getMetamodel();
EntityType entityType_ = metamodel.entity( pClass );
Root<?> root = criteriaQuery.from( pClass );

Expression pathName = root.get( entityType_.getSingularAttribute( "name" ));
Path pathDate = root.get( entityType_.getSingularAttribute( "date" ) );
Expression<Date> pathTruncatedDate = criteriaBuilder.function("trunc", Date.class , pathDate, 'MM');
Expression pathNumber = criteriaBuilder.sum( root.get( entityType_.getSingularAttribute("number") ));

criteriaQuery.select( criteriaBuilder.array( pathName,  pathTruncatedDate, pathNumber) );

List<Expression> groupBy = new ArrayList<Expression>();
groupBy.add( pathName);
groupBy.add( pathTruncatedDate);
criteriaQuery.groupBy( groupBy );
TypedQuery<Object[]> q = em.createQuery( criteriaQuery );

<-- and surprisingly I got ORA-00979: not a GROUP BY expression by running it. What coused the problem, everything is on right place, i guess. Thank You very much in advance!

SzGyD
  • 88
  • 7

2 Answers2

0

it's not look like Oracle question but,
what about creating view by your statement and using view in your application?
something like

create view vw_my_table as
select name, trunc( date, 'MM' ), sum( number )
from t$mytable
group by name, trunc( date, 'MM' );
Galbarad
  • 461
  • 3
  • 16
  • thank You for your response. I made the view, and selected from that, which runs perfectly right away. However I can't figure out why does not work without additional view table. I'd rather skip solution using view table. Do you have any other idea? – SzGyD Nov 13 '13 at 09:58
  • suppose it's some restriction of criteriaBuilder I think that if you will use simple query it should work fine – Galbarad Nov 13 '13 at 10:00
0

I have same issue and have found the root cause of this ORA-00979.

Oracle automatically handle aggregation of date in below query

select name, trunc( date, 'MM' ), sum( number )
from t$mytable
group by name, trunc( date, 'MM' );

With Criteria API you need to use criteria builder least method.

In your cas you just need to replace select line with:

criteriaQuery.select( criteriaBuilder.array( pathName,  criteriaBuilder.least(pathTruncatedDate), pathNumber) );
Mateusz
  • 1