java - HQL IF 子句不适用于 IS NOT NULL

Question

我有以下 HQL 查询。

@Query("SELECT IF ( job.doneDate IS NOT NULL ) THEN SUM( jobCost.amount ) FROM JobCost AS jobCost "
        + "LEFT JOIN jobCost.job AS job WHERE job.code = :#{#filter.jobCode} "
        + "AND jobCost.costType = :#{#filter.jobCostType} "
        + "AND jobCost.job.business.id = :#{#filter.searchBusiness} "
        + "AND jobCost.date >= :job.doneDate "
        + "AND jobCost.date <= :#{#filter.endDate} ELSE 0.00 END")

我在控制台上收到此错误。

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: THEN near line 1, column 40 [SELECT IF ( job.doneDate IS NOT NULL ) THEN SUM( jobCost.amount ) FROM com.job.JobCost AS jobCost LEFT JOIN jobCost.job AS job WHERE job.code = :__$synthetic$__1 AND jobCost.costType = :__$synthetic$__2 AND jobCost.job.business.id = :__$synthetic$__3 AND jobCost.date >= :job.doneDate AND jobCost.date <= :__$synthetic$__4 ELSE 0.00 END]

我应该如何纠正这个问题？

Answer 1

@Query("SELECT CASE WHEN job.doneDate IS NOT NULL THEN SUM(jobCost.amount) ELSE 0.00 END FROM JobCost AS jobCost "
    + "LEFT JOIN jobCost.job AS job WHERE job.code = :#{#filter.jobCode} "
    + "AND jobCost.costType = :#{#filter.jobCostType} "
    + "AND jobCost.job.business.id = :#{#filter.searchBusiness} "
    + "AND jobCost.date >= :job.doneDate "
    + "AND jobCost.date <= :#{#filter.endDate}")

请注意:ELSE 在 WHERE 条件下 Not Acceptable ，您可以通过 CASE WHEN IS NOT NULL THEN .. ELSE ...END 来处理。

请引用:https://docs.jboss.org/hibernate/orm/3.5/reference/en/html/queryhql.html