This week's book giveaway is in the Java 9 forum. We're giving away four copies of Java 9 Modularity: Patterns and Practices for Developing Maintainable Applications and have Sander Mak & Paul Bakker on-line! See this thread for details.
HQL and JPQL are not the same as SQL. They are far more limited, and don't support everything that's supported in SQL. What you have there is one of those things. But you can use a native query combined with result set mappings to achieve the same thing.