HQL IF子句not work with 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}")

请注意:在WHERE条件下ELSE是不可接受的，您可以在不为NULL THEN的情况下通过CASE处理此问题。否则...END。

请参阅：https://docs.jboss.org/hibernate/orm/3.5/reference/en/html/queryhql.html