How to fix H2 Syntax error in SQL statement?

I asked earlier today about replacing a window function because H2 does not support it.

I rewrote SQL query but every time I get syntax error in the SQL query (posted below)

expected "UNION, MINUS, EXCEPT, INTERSECT, ORDER, OFFSET, FETCH, LIMIT, FOR, ).

Can you please tell where is error in this query?

@Query(value = "
SELECT se.id, se.code, se.created, se.ecid, se.psid
FROM (
    SELECT DISTINCT ps1, created1, created2 
    FROM ( 
        SELECT tmp1.code1, tmp1.created1, tmp1.ec1, tmp1.ps1, tmp2.code2, tmp2.created2, tmp2.ec2, tmp2.ps2, 
            MIN(tmp2.created2) OVER (PARTITION BY tmp2.ec2, tmp2.ps2, tmp1.created1) AS closest 
        FROM (
            (SELECT code as code1, created as created1, ecid as ec1, psid as ps1 
            FROM tableRE 
            WHERE code = ?1 
            GROUP BY code1, created1, ec1, ps1
            ) tmp1 
            LEFT JOIN 
                (SELECT code as code2, created as created2, ecid as ec2, psid as ps2 
                FROM tableRE) tmp2 
            ON tmp2.ps2 = tmp1.ps1 AND tmp2.ec2 = tmp1.ec1 AND tmp1.created1 < tmp2.created2
        )
        ORDER BY tmp1.created1
    ) tmpRes 
    WHERE tmpRes.created2 = tmpRes.closest OR tmpRes.closest IS NULL
) res 
LEFT JOIN tableSE se ON se.created > res.created1 
    AND (CASE WHEN res.created2 is null THEN CURRENT_TIMESTAMP ELSE res.created2 END) > se.created 
    AND se.psid = res.ps1 
ORDER BY se.created DESC", nativeQuery = true)

Error in Intellij:

2019-07-03 07:59:58.817 ERROR 22340 --- [           main] o.h.e.j.s.SqlExceptionHelper             : Syntax error in SQL statement "SELECT SE.ID, SE.CODE, SE.CREATED, SE.ECID, SE.PSID FROM (SELECT DISTINCT PS1, CREATED1, CREATED2 FROM ( SELECT TMP1.CODE1, TMP1.CREATED1, TMP1.EC1, TMP1.PS1, TMP2.CODE2, TMP2.CREATED2, TMP2.EC2, TMP2.PS2, MIN(TMP2.CREATED2) OVER (PARTITION BY TMP2.EC2, TMP2.PS2, TMP1.CREATED1) AS CLOSEST FROM((SELECT CODE AS CODE1, CREATED AS CREATED1, ECID AS EC1, PSID AS PS1 FROM TABLE1 WHERE CODE = ? GROUP BY CODE1, CREATED1, EC1, PS1) TMP1[*] LEFT JOIN (SELECT CODE AS CODE2, CREATED AS CREATED2, ECID AS EC2, PSID AS PS2 FROM TABLE1) TMP2 ON TMP2.PS2 = TMP1.PS1 AND TMP2.EC2 = TMP1.EC1 AND TMP1.CREATED1 < TMP2.CREATED2)ORDER BY TMP1.CREATED1) TMPRES WHERE TMPRES.CREATED2 = TMPRES.CLOSEST OR TMPRES.CLOSEST IS NULL) RES LEFT JOIN TABLE2 SE ON SE.CREATED > RES.CREATED1 AND (CASE WHEN RES.CREATED2 IS NULL THEN CURRENT_TIMESTAMP ELSE RES.CREATED2 END) > SE.CREATED AND SE.PSID = RES.PS1 ORDER BY SE.CREATED DESC "; expected "UNION, EXCEPT, MINUS, INTERSECT, ORDER, OFFSET, FETCH, LIMIT, SAMPLE_SIZE, FOR, )"; SQL statement:
SELECT se.id, se.code, se.created, se.ecid, se.psid FROM (SELECT DISTINCT ps1, created1, created2 FROM ( SELECT tmp1.barcode1, tmp1.created1, tmp1.ec1, tmp1.ps1, tmp2.barcode2, tmp2.created2, tmp2.ec2, tmp2.ps2, MIN(tmp2.created2) OVER (PARTITION BY tmp2.ec2, tmp2.ps2, tmp1.created1) AS closest FROM((SELECT code as code1, created as created1, ecid as ec1, psid as ps1 FROM table1 WHERE code = ? GROUP BY code1, created1, ec1, ps1) tmp1 LEFT JOIN (SELECT code as code2, created as created2, ecid as ec2, psid as ps2 FROM table1) tmp2 ON tmp2.ps2 = tmp1.ps1 AND tmp2.ec2 = tmp1.ec1 AND tmp1.created1 < tmp2.created2) ORDER BY tmp1.created1) tmpRes WHERE tmpRes.created2 = tmpRes.closest OR tmpRes.closest IS NULL) res LEFT JOIN table2 se on se.created > res.created1 and (CASE WHEN res.created2 IS NULL THEN CURRENT_TIMESTAMP ELSE res.created2 END) > se.created and se.psid= res.ps1 ORDER BY se.created DESC [42001-199]
2019-07-03 07:59:58.838 ERROR 22340 --- [           main] s.o.t.a.ExceptionResolver                : could not prepare statement; SQL [SELECT se.id, se.code, se.created, se.ecid, se.psid FROM (SELECT DISTINCT ps1, created1, created2 FROM ( SELECT tmp1.barcode1, tmp1.created1, tmp1.ec1, tmp1.ps1, tmp2.barcode2, tmp2.created2, tmp2.ec2, tmp2.ps2, MIN(tmp2.created2) OVER (PARTITION BY tmp2.ec2, tmp2.ps2, tmp1.created1) AS closest FROM((SELECT code as code1, created as created1, ecid as ec1, psid as ps1 FROM table1 WHERE code = ? GROUP BY code1, created1, ec1, ps1) tmp1 LEFT JOIN (SELECT code as code2, created as created2, ecid as ec2, psid as ps2 FROM table1) tmp2 ON tmp2.ps2 = tmp1.ps1 AND tmp2.ec2 = tmp1.ec1 AND tmp1.created1 < tmp2.created2) ORDER BY tmp1.created1) tmpRes WHERE tmpRes.created2 = tmpRes.closest OR tmpRes.closest IS NULL) res LEFT JOIN table2 se on se.created > res.created1 and (CASE WHEN res.created2 IS NULL THEN CURRENT_TIMESTAMP ELSE res.created2 END) > se.created and se.psid= res.ps1 ORDER BY se.created DESC]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [SELECT se.id, se.code, se.created, se.ecid, se.psid FROM (SELECT DISTINCT ps1, created1, created2 FROM ( SELECT tmp1.barcode1, tmp1.created1, tmp1.ec1, tmp1.ps1, tmp2.barcode2, tmp2.created2, tmp2.ec2, tmp2.ps2, MIN(tmp2.created2) OVER (PARTITION BY tmp2.ec2, tmp2.ps2, tmp1.created1) AS closest FROM((SELECT code as code1, created as created1, ecid as ec1, psid as ps1 FROM table1 WHERE code = ? GROUP BY code1, created1, ec1, ps1) tmp1 LEFT JOIN (SELECT code as code2, created as created2, ecid as ec2, psid as ps2 FROM table1) tmp2 ON tmp2.ps2 = tmp1.ps1 AND tmp2.ec2 = tmp1.ec1 AND tmp1.created1 < tmp2.created2) ORDER BY tmp1.created1) tmpRes WHERE tmpRes.created2 = tmpRes.closest OR tmpRes.closest IS NULL) res LEFT JOIN table2 se on se.created > res.created1 and (CASE WHEN res.created2 IS NULL THEN CURRENT_TIMESTAMP ELSE res.created2 END) > se.created and se.psid= res.ps1 ORDER BY se.created DESC]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:242) ~[spring-orm-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225) ~[spring-orm-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527) ~[spring-orm-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at com.sun.proxy.$Proxy148.getAllProductsByMaterialUsage(Unknown Source) ~[?:?]
    at sk.optotune.trackerengine.webapp.server.events.EventTimelineServiceImpl.getMaterialUsage(EventTimelineServiceImpl.java:74) ~[classes/:?]
    at sk.optotune.trackerengine.webapp.server.events.EventTimelineServiceImpl$$FastClassBySpringCGLIB$$59afe36d.invoke(<generated>) ~[classes/:?]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at sk.optotune.trackerengine.webapp.server.events.EventTimelineServiceImpl$$EnhancerBySpringCGLIB$$687e3773.getMaterialUsage(<generated>) ~[classes/:?]
    at sk.optotune.trackerengine.webapp.server.events.EventController.getMaterials(EventController.java:79) ~[classes/:?]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_201]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_201]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_201]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_201]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) [spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) [spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974) [spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:866) [spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) [tomcat-embed-core-8.5.31.jar:8.5.31]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851) [spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:71) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) [tomcat-embed-core-8.5.31.jar:8.5.31]
    at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:166) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.debug.DebugFilter.invokeWithWrappedRequest(DebugFilter.java:90) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.security.web.debug.DebugFilter.doFilter(DebugFilter.java:77) [spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:133) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:165) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at sk.optotune.trackerengine.application.events.EventTimelineServiceImplTest.getMaterialUsageForGivenValidBarcode(EventTimelineServiceImplTest.java:238) [test-classes/:?]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_201]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_201]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_201]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_201]
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) [junit-4.12.jar:4.12]
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) [junit-4.12.jar:4.12]
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) [junit-4.12.jar:4.12]
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:73) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:83) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) [junit-4.12.jar:4.12]
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363) [junit-4.12.jar:4.12]
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190) [spring-test-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137) [junit-4.12.jar:4.12]
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68) [junit-rt.jar:?]
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47) [junit-rt.jar:?]
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242) [junit-rt.jar:?]
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) [junit-rt.jar:?]
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:181) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:147) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1985) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1915) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:938) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2692) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2675) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2502) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2200) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1016) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:152) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.Query.getResultList(Query.java:146) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:129) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:590) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    ... 116 more

Solution 1:

H2 supports window functions since 1.4.198. If you can upgrade H2 to a latest version (1.4.199) it's better to do it instead of such workarounds with overcomplicated queries, they will be slower than a simple query with a window function.

Note that FROM table) part is invalid in recent versions of H2, because TABLE is a keyword, if you have a table with that name it needs to be quoted ("TABLE" or "table" depending on your settings).

If you're really required to use some old unsupported version of H2 such as 1.4.197 or older, you need to post the complete error message here. It includes your SQL command with a [*] mark that indicates a place with a syntax problem.