title: Using the 'case...when...then...else...end' construct in the 'having' clause in JPA criteria query
body: <p>The following criteria query calculates the average of rating of different groups of products.</p>
  
  <pre><code>CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
  CriteriaQuery&lt;Tuple&gt;criteriaQuery=criteriaBuilder.createQuery(Tuple.class);
  Metamodel metamodel=entityManager.getMetamodel();
  EntityType&lt;Product&gt;entityType=metamodel.entity(Product.class);
  Root&lt;Product&gt;root=criteriaQuery.from(entityType);
  SetJoin&lt;Product, Rating&gt; join = root.join(Product_.ratingSet, JoinType.LEFT);
  
  Expression&lt;Number&gt; quotExpression = criteriaBuilder.quot(criteriaBuilder.sum(join.get(Rating_.ratingNum)), criteriaBuilder.count(join.get(Rating_.ratingNum)));
  Expression&lt;Integer&gt; roundExpression = criteriaBuilder.function("round", Integer.class, quotExpression);
  Expression&lt;Object&gt; selectExpression = criteriaBuilder.selectCase().when(quotExpression.isNull(), 0).otherwise(roundExpression );
  
  criteriaQuery.select(criteriaBuilder.tuple(root.get(Product_.prodId).alias("prodId"), selectExpression.alias("rating")));
  criteriaQuery.groupBy(root.get(Product_.prodId));
  
  criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(roundExpression, 0));
  criteriaQuery.orderBy(criteriaBuilder.desc(root.get(Product_.prodId)));
  
  TypedQuery&lt;Tuple&gt; typedQuery = entityManager.createQuery(criteriaQuery);
  List&lt;Tuple&gt; tuples = typedQuery.getResultList();
  </code></pre>
  
  <p>It generates the following SQL query : </p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT product0_.prod_id AS col_0_0_, 
         CASE 
           WHEN Sum(ratingset1_.rating_num) / Count(ratingset1_.rating_num) IS 
                NULL THEN 
           0 
           ELSE Round(Sum(ratingset1_.rating_num) / Count(ratingset1_.rating_num)) 
         END AS col_1_0_ 
  FROM   social_networking.product product0_ 
         LEFT OUTER JOIN social_networking.rating ratingset1_ 
                      ON product0_.prod_id = ratingset1_.prod_id 
  GROUP  BY product0_.prod_id 
  HAVING Round(Sum(ratingset1_.rating_num) / Count(ratingset1_.rating_num)) &gt;= 0 
  ORDER  BY product0_.prod_id DESC 
  </code></pre>
  
  <p>The <code>case...when</code> structure replaces <code>null</code> values with <code>0</code>, if the specified expression in the <code>case</code> clause is evaluated to <code>null</code>.</p>
  
  <p>I need the same <code>case...when</code> construct in the <code>having</code> clause so that the group of rows returned by the <code>group by</code> clause can be filtered by replacing <code>null</code> with <code>0</code> in the list of values calculated by the <code>case...when</code> construct, if any. </p>
  
  <p>Accordingly, the <code>having</code> clause should be generated like</p>
  
  <pre class="lang-sql prettyprint-override"><code>HAVING
      (CASE
          WHEN Sum(ratingset1_.rating_num)/Count(ratingset1_.rating_num) IS
               NULL THEN 0 
          ELSE Round(sum(ratingset1_.rating_num)/Count(ratingset1_.rating_num))
      END)&gt;=0
  </code></pre>
  
  <p>It could be possible, if in the <code>greaterThanOrEqualTo()</code> method, <code>selectExpression</code> instead of <code>roundExpression</code> is given but it is not possible. Doing so, generates a compile-time error indicating type mismatch between <code>Expression&lt;Integer&gt;</code> and <code>Expression&lt;Object&gt;</code>.</p>
  
  <p>So how can I have the same <code>case...when</code> structure  in the <code>having</code> clause as in the <code>select</code> clause?</p>
  
  <p><sub>I have also tried by removing the generic type parameter <code>Object</code> of the expression like <code>Expression selectExpression</code> but doing so, caused the <code>NullPointerException</code> to be thrown.</sub></p>
  
  <hr>
  
  <p>Moreover, alias names (<code>prodId</code>, <code>rating</code>) as given in the <code>select</code> clause have no effect in the generated SQL as can be seen. Why columns are not aliased here? Am I missing something?</p>
  
  <p>If columns are aliased then, it should be possible to write the <code>having</code> clause just like follows.</p>
  
  <pre><code>having rating&gt;=0
  </code></pre>
  
  <p>and <code>having</code> in the criteria query should be as follows,</p>
  
  <pre><code>criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(join.&lt;Integer&gt;get("rating"), 0));
  </code></pre>
  
  <p>but as columns are not aliased in the <code>select</code> clause, it throws an exception.</p>
  
  <pre><code>java.lang.IllegalArgumentException: Unable to resolve attribute [rating] against path [null]
  </code></pre>
  
  <hr>
  
  <p>What is the way to get around this situation? Anyway, the rows returned by <code>Group by</code> should be filtered by replacing <code>null</code> with <code>0</code> in the list of values produced by <code>case...when</code> in the <code>select</code> clause.</p>
  
  <hr>
  
  <p>I'm using JPA 2.0 provided by Hibernate 4.2.7 final.</p>
  
  <hr>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I have tried with the following expression :</p>
  
  <pre><code>Expression&lt;Integer&gt; selectExpression = criteriaBuilder.&lt;Integer&gt;selectCase()
                                         .when(quotExpression.isNull(), 0)
                                         .&lt;Integer&gt;otherwise(roundExpression);
  </code></pre>
  
  <p>but it caused the following exception to be thrown : </p>
  
  <pre><code>Caused by: java.lang.NullPointerException
      at java.lang.Class.isAssignableFrom(Native Method)
      at org.hibernate.ejb.criteria.ValueHandlerFactory.isNumeric(ValueHandlerFactory.java:69)
      at org.hibernate.ejb.criteria.predicate.ComparisonPredicate.&lt;init&gt;(ComparisonPredicate.java:69)
      at org.hibernate.ejb.criteria.CriteriaBuilderImpl.greaterThanOrEqualTo(CriteriaBuilderImpl.java:468)
  </code></pre>
  
  <p>How can the following expression work then,</p>
  
  <pre><code>Expression&lt;Integer&gt; roundExpression = criteriaBuilder
                                .function("round", Integer.class, quotExpression);
  </code></pre>
  
  <p>both have the same type?</p>
  
  <p>Is there a way to put the <code>case...when</code> structure in the <code>having</code> clause?</p>
  
  <hr>
  
  <p><strong>EDIT</strong></p>
  
  <p>Changing the expression type to </p>
  
  <pre><code>Expression&lt;Integer&gt; selectExpression = criteriaBuilder
                                         .&lt;Integer&gt;selectCase()
                                         .when(quotExpression.isNull(), 0)
                                         .&lt;Integer&gt;otherwise(roundExpression);
  </code></pre>
  
  <p>in <strong>EclipseLink (2.3.2)</strong> works hence, it can be made available in the <code>having</code> clause. </p>
  
  <p>In case of Hibernate provider, it throws the <code>NullPoiterExcpetion</code>, if an attempt is made to change the expression type of <code>selectCase()</code> (which returns <code>Expression&lt;Object&gt;</code> by default).</p>
  
  <hr>
  
  <p><strong>Update :</strong></p>
  
  <p>This issue still persists in Hibernate 5.0.5 final.</p>