title: ORA-00905: missing keyword Hibernate Query
body: <p>I am trying to execute a Hibernate Query in JAVA. The query is as follows.</p>
  
  <pre><code>query.append(" SELECT INV.skuNum as skuNum , ITM.regDes as des, ITM.dfltWhsUom as uom, (CASE WHEN ITM.billQty IS NULL THEN 0 ELSE (CASE WHEN INV.rqstQty IS NULL THEN 0 ELSE (INV.rqstQty/ITM.billQty) END) END) AS qty, ")
      .append(" INV.fromLocCd as fromLocCd , INV.toLocCd as toLocCd , INV.empInit as empInit , INV.priority as priority , ITM.des1 as des1, ITM.des2 as des2 , ITM.veCd as veCd, ITM.divCd as divCd , ITM.deptCd as deptCd , ITM.classCd as classCd , ITM.subclassCd as subClassCd " )
      .append(" FROM InvMvmt INV")
      .append(" LEFT JOIN GmSku SKU with SKU.skuNum = INV.skuNum ")
      .append(" LEFT JOIN GmItm ITM with ITM.itmCd = SKU.itmCd ")
      .append(" WHERE 1=1 ")          
      .append(" AND INV.statCd = '&lt;SOMETHING&gt;' AND INV.mvmtTp = '&lt;SOMETHING&gt;' ")
      .append((sWhsStoreCd != null) ? " AND INV.storeCd = '"+sWhsStoreCd+"' ": ")
      .append((sCtrlNum != null ) ? " AND INV.ctrlNum ='"+sCtrlNum+"' " : "")
      .append((sBatchNum != null ) ? " AND INV.batchNum ='"+sBatchNum+"'" : "")
      .append((sEmpInit != null ) ? " AND INV.empInit = '"+sEmpInit+"'" : "")
      .append((sSkuNum != null ) ? " AND INV.skuNum = '"+sSkuNum+"'" : "")
  </code></pre>
  
  <p>I am getting the following error when I execute the above HQL or Hibernate Query</p>
  
  <pre><code>Hibernate: select * from ( select row_.*, rownum rownum_ from ( SELECT INV.skuNum as skuNum , ITM.regDes as des, ITM.dfltWhsUom as uom, (CASE WHEN ITM.billQty IS NULL THEN 0 ELSE (CASE WHEN INV.rqstQty IS NULL THEN 0 ELSE (INV.rqstQty/ITM.billQty) END) END) AS qty,  INV.fromLocCd as fromLocCd , INV.toLocCd as toLocCd , INV.empInit as empInit , INV.priority as priority , ITM.des1 as des1, ITM.des2 as des2 , ITM.veCd as veCd, ITM.divCd as divCd , ITM.deptCd as deptCd , ITM.classCd as classCd , ITM.subclassCd as subClassCd  FROM InvMvmt INV LEFT JOIN GmSku SKU with SKU.skuNum = INV.skuNum  LEFT JOIN GmItm ITM with ITM.itmCd = SKU.itmCd  WHERE 1=1  AND INV.statCd = 'OPEN' AND INV.mvmtTp = 'COL'  AND INV.storeCd = '000'  ORDER BY INV.skuNum ) row_ ) where rownum_ &lt;= ? and rownum_ &gt; ?
  [ WARN] [http-8080-3 02:09:35] (JDBCExceptionReporter.java:logExceptions:77) SQL Error: 905, SQLState: 42000
  [ERROR] [http-8080-3 02:09:35] (JDBCExceptionReporter.java:logExceptions:78) ORA-00905: missing keyword
  
  [ERROR] [http-8080-3 02:09:35] (COLMController.java:COLMgetTableDataInSearch:96) Exception occured in the method COLMgetTableDataInSearch in file COLMController
  org.hibernate.exception.SQLGrammarException: could not execute query
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      at org.hibernate.loader.Loader.doList(Loader.java:2214)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
      at org.hibernate.loader.Loader.list(Loader.java:2090)
      at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
      at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
      at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
      at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
      at com.stc.res.dao.COLMDaoImpl.COLMSearch(COLMDaoImpl.java:211)
      at com.stc.res.dao.COLMDaoImpl$$FastClassByCGLIB$$9729c42.invoke(&lt;generated&gt;)
      at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
      at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
      at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
      at com.stc.res.dao.COLMDaoImpl$$EnhancerByCGLIB$$8c8840f1_2.COLMSearch(&lt;generated&gt;)
      at com.stc.res.controller.COLMController.COLMgetTableDataInSearch(COLMController.java:76)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:601)
      at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
      at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)
      at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)
      at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
      at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
      at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
      at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:368)
      at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
      at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:109)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:169)
      at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
      at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
      at java.lang.Thread.run(Thread.java:722)
  Caused by: java.sql.SQLException: ORA-00905: missing keyword
  
  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
      at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415)
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
      at org.hibernate.loader.Loader.doQuery(Loader.java:662)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
      at org.hibernate.loader.Loader.doList(Loader.java:2211)
      ... 68 more
  org.hibernate.exception.SQLGrammarException: could not execute query
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      at org.hibernate.loader.Loader.doList(Loader.java:2214)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
      at org.hibernate.loader.Loader.list(Loader.java:2090)
      at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
      at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
      at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
      at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
      at com.stc.res.dao.COLMDaoImpl.COLMSearch(COLMDaoImpl.java:211)
      at com.stc.res.dao.COLMDaoImpl$$FastClassByCGLIB$$9729c42.invoke(&lt;generated&gt;)
      at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
      at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
      at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
      at com.stc.res.dao.COLMDaoImpl$$EnhancerByCGLIB$$8c8840f1_2.COLMSearch(&lt;generated&gt;)
      at com.stc.res.controller.COLMController.COLMgetTableDataInSearch(COLMController.java:76)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:601)
      at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
      at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)
      at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)
      at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
      at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
      at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
      at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:368)
      at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
      at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:109)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
      at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:169)
      at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
      at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
      at java.lang.Thread.run(Thread.java:722)
  Caused by: java.sql.SQLException: ORA-00905: missing keyword
  
  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
      at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415)
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
      at org.hibernate.loader.Loader.doQuery(Loader.java:662)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
      at org.hibernate.loader.Loader.doList(Loader.java:2211)
      ... 68 more
  </code></pre>
  
  <p>Please help me with this error. I have googled about Joins in Hibernate and tried numerous combinations of INNER, normal JOIN, LEFT JOIN but seems to be nothing is working for me. Any suggestions is welcome.</p>