Stack Exchange
expecting CLOSE, found 'WHEN' near line 1 exception? How to get rid of it in HIbernate
I'm using a WHEN CASE condition in my query in Java Hibernate.The query works fine in MySQL workbench,but gives me this exception in the Java Console.
:org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found 'WHEN' near line 1.

This is my query,
StringBuffer hql = new StringBuffer();


hql.append(" SELECT ");
            hql.append(" v.year,");
            hql.append(" v.number,");
            hql.append(" ap.property,");
            hql.append(" CASE");
            hql.append(" WHEN ap.id = 1  THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 2  THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 3  THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 4  THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 5  THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 6  THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 7  THEN AVG(v.Month101)");
            hql.append(" WHEN ap.id = 8  THEN AVG(v.Month101)");
            hql.append(" WHEN ap.id = 9  THEN AVG(v.Month101)");
            hql.append(" WHEN ap.id = 10 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 11 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 12 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 13 THEN AVG(v.Month101)");
            hql.append(" WHEN ap.id = 14 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 15 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 16 THEN AVG(v.Month101)");
            hql.append(" WHEN ap.id = 17 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 18 THEN AVG(v.Month101)");
            hql.append(" WHEN ap.id = 19 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 20 THEN AVG(v.Month101)");
            hql.append(" WHEN ap.id = 21 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 22 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 23 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 24 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 25 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 26 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 27 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 28 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 29 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 30 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 31 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 32 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 33 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 34 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 35 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 36 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 37 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 38 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 39 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 40 THEN SUM(v.Month101)");
            hql.append(" WHEN ap.id = 41 THEN SUM(v.Month101)");
            hql.append(" END AS jan,");
            hql.append(" FROM");
            hql.append(" table1 v,");
            hql.append(" table2 ap");
            hql.append(" WHERE");
            hql.append(" v.number=01");
            hql.append(" AND v.Id = ap.id");
            hql.append(" AND v.year =2015");
            hql.append(" GROUP BY ap.property");
            hql.append(" ORDER BY v.Id");

I have this conditions for 12 months,Whats wrong with the syntax?Can any one explain?

END AS jan, There is syntax error here. Remove comma and try it.

@Samir,I think I would need it,I have similar conditions for 12 months,after 41 WHEN-CASE condiotions,I'll again have END as feb,END as mar and so on

Okay. So ensure after last Date of Dec, there is no extra comma.

Please don't use StringBuffer as it was replaced by StringBuilder more than ten years ago. In this case it would be short and more efficient to use + between the Strings.

@PeterLawrey,Thanks for the suggestion.I would implement that.

I think you have a syntax error due to a stray comma after as END AS jan
hql.append(" END AS jan,");
hql.append(" FROM");

Remove the comma after jan above and check again - I guess it would work.

,I think I would need it,I have similar conditions for 12 months,after 41 WHEN-CASE condiotions,I'll again have END as feb,END as mar and so on.I do not have comma after END as dec,after the last CASE-WHEN condition.

@NikhilBharadwaj - How about just removing it anyway?  There should not be a comma before the FROM.

@Stephen,Yes you are right. There should not be a comma before FROM

Found the syntax error. Had missed one closing bracket SUM(v.Month101 ");
Thanks for the suggestions.
