JPQL Query Exception with between

Support
#1

I want to take last week data from current date in JPQL,but I used below query it is not working

SELECT e.startTime 
  FROM worklog$WorkLog e where
e.startTime BETWEEN CURRENT_DATE AND CURRENT_DATE - 7 DAY
#2

Hi,

There is no between keyword in JPQL. You can use @between macro described here: Macros in JPQL - CUBA Platform. Developer’s Manual

Example:

select c from sales$Customer where @between(c.createTs, now, now+1, day)
#3

If I run this query,I get NullPointerException

select w1.startTime,sum(w1.duration_in_milli_second) time
from worklog$WorkLog wl where 
@between(w1.startTime,now-5,now, day,user_timezone) group by w1.startTime
order by w1.startTime
#4

Could you please provide exceptions for your questions? It is really hard to help you without details.

And also, please use code blocks for your code.

image

#5

This is the error I am getting

java.lang.NullPointerException
	at com.haulmont.cuba.core.global.QueryParserAstBased.getQueryPaths(QueryParserAstBased.java:208)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)
	at com.haulmont.cuba.core.sys.PerformanceLogInterceptor.aroundInvoke(PerformanceLogInterceptor.java:29)
	at sun.reflect.GeneratedMethodAccessor99.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:629)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:618)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
	at com.sun.proxy.$Proxy269.getQueryPaths(Unknown Source)
	at com.haulmont.cuba.core.app.RdbmsStore.checkValueQueryPermissions(RdbmsStore.java:751)
	at com.haulmont.cuba.core.app.RdbmsStore.loadValues(RdbmsStore.java:472)
	at com.haulmont.cuba.core.app.DataManagerBean.loadValues(DataManagerBean.java:259)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.haulmont.cuba.core.app.DataManagerBean$SecureDataManagerInvocationHandler.invoke(DataManagerBean.java:362)
	at com.sun.proxy.$Proxy512.loadValues(Unknown Source)
	at com.haulmont.cuba.core.app.DataServiceBean.loadValues(DataServiceBean.java:61)
#7

You use different aliases w1 and wl. It causes a problem. Use only one alias.