• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Subqueries in JPA is not working with OpenJPA

 
                      
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does JPA support subqueries in the select part of the main query.

For example :

SELECT record1.txnId, record1.accessTime, (SELECT record2.accessTime FROM Metering record2 WHERE record2.txnId=record1.txnId AND record2.action='END') FROM Metering record1 WHERE record1.action='START'

The above query is properly working with Hibernate implementation. But when i use OpenJPA implementation instead of Hibernate implementation it is giving the following error:

org.springframework.dao.InvalidDataAccessApiUsageException: Encountered "(" at character 44, but expected: ["ABS", "AVG", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "LENGTH", "LOCATE", "LOWER", "MAX", "MIN", "MOD", "NEW", "OBJECT", "SIZE", "SQRT", "SUBSTRING", "SUM", "TRIM", "UPPER", <IDENTIFIER>].; nested exception is <openjpa-1.2.1-r752877:753278 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Encountered "(" at character 44, but expected: ["ABS", "AVG", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "LENGTH", "LOCATE", "LOWER", "MAX", "MIN", "MOD", "NEW", "OBJECT", "SIZE", "SQRT", "SUBSTRING", "SUM", "TRIM", "UPPER", <IDENTIFIER>].
 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
not sure.....but you might want to give an alias to the term that you have selected like

SELECT record1.txnId, record1.accessTime, (SELECT record2.accessTime FROM Metering record2 WHERE record2.txnId=record1.txnId AND record2.action='END') AS accessTime FROM Metering record1 WHERE record1.action='START'
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic