请求在SQL/oracle上工作，但不在java Hibernate HQL上工作

Question

我有以下在oracle中运行良好的请求：

SELECT SUM(col1_sum + col2_sum + col3_sum) AS sum_total FROM ( 
   SELECT 
      COUNT(CASE WHEN (col1 != 0) THEN 1 END) AS col1_sum, 
      COUNT(CASE WHEN (col2 IS NULL) THEN 1 END) AS col2_sum, 
      COUNT(CASE WHEN (col3 IS NULL OR col 3 < 0) THEN 1 END) AS col3_sum 
    FROM myTable)
;

它会根据我的情况返回正确的金额

但当我把它翻译成HQL时，就像这样：

@Query("SELECT SUM(col1_sum + col2_sum + col3_sum) AS sum_total FROM ( "
   +"SELECT "
   +" COUNT(CASE WHEN (col1 != 0) THEN 1 END) AS col1_sum, "
   +" COUNT(CASE WHEN (col2 IS NULL) THEN 1 END) AS col2_sum, "
   +" COUNT(CASE WHEN (col3 IS NULL OR col 3 < 0) THEN 1 END) AS col3_sum "
   +"FROM myTable)")

我可以毫无错误地编译我的项目，但当我启动它时，hibernate出现了以下错误：

2018-03-08 11:18:54.485 [NO_USER NO_SESSION] ERROR o.h.h.i.a.ErrorCounter : line 1:96: unexpected token: ( antlr.NoViableAltException: unexpected token: ( at org.hibernate.hql.internal.antlr.HqlBaseParser.fromRange(HqlBaseParser.java:1544) [....] Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 96 [...]

显然，hibernate不会在第一行的FROM (之后重新确认我的查询

有没有办法将我的Oracle/SQL请求正确转换为Hibernate/HQL

Answer 1

这是因为Hibernate不支持FROM子句中的子查询。请参阅讨论Cannot use subqueries in from clause. Is there an alternativ

您可能需要使用@NamedNativeQuery来传递本机SQL，这样您就可以在FROM子句中使用子查询，或者看到您在FROM子句中重写查询而不使用子查询