Hi,
I am trying to calculate distance between two lat and long using JPQL queries but i am not able to execute the same as i am getting JpqlSyntaxException but the same is working with sql queries.
Is it possible to acheive the same with JPQL since using JPQL i can load the data to workbench and also able to use pagination and Search condition filters. If i can use distance calculation with any other form like entity query then can i able to user pagination and other feature of datamanager.
Please share if any other demo example are available for the same.
io.jmix.data.impl.jpql.JpqlSyntaxException: Errors found for input jpql:[SELECT e FROM Cases e LEFT JOIN UserCaseAllocation a ON e.caseId = a.caseId LEFT JOIN Collections c ON e.caseId = c.caseId where (a.caseId IS NULL OR a.toUser in :userIdList) and lower(e.vertical) = :vertical and e.deleteFlag = 'F' and e.column6 is not null and c.latitude is not null and ( 6371.0 * 2 * ASIN(SQRT( POWER(SIN(RADIANS(cast(e.column6 as double precision) - cast(c.latitude as double precision)) / 2), 2) + POWER(SIN(RADIANS(cast(e.column7 as double precision) - cast(c.longitude as double precision)) / 2), 2) * COS(RADIANS(cast(c.latitude as double precision))) * COS(RADIANS(cast(e.column6 as double precision))) )) <= 100 )]
CommonErrorNode [<mismatched token: [@171,385:385='-',<67>,1:385], resync=SELECT e FROM Cases e LEFT JOIN UserCaseAllocation a ON e.caseId = a.caseId LEFT JOIN Collections c ON e.caseId = c.caseId
where (a.caseId IS NULL OR a.toUser in :userIdList) and lower(e.vertical) = :vertical and e.deleteFlag = âFâ and e.column6 is not null and c.latitude is not null and ( 6371.0 * 2 * ASIN(SQRT( POWER(SIN(RADIANS(cast(e.column6 as double precision) - cast(c.latitude as double precision)) / 2), 2) + POWER(SIN(RADIANS(cast(e.column7 as double precision) - cast(c.longitude as double precision)) / 2), 2) * COS(RADIANS(cast(c.latitude as double precision))) * COS(RADIANS(cast(e.column6 as double precision))) )) <= 100 )>]
at io.jmix.data.impl.jpql.Parser.checkTreeForExceptions(Parser.java:112)
at io.jmix.data.impl.jpql.Parser.parse(Parser.java:40)
at io.jmix.data.impl.jpql.QueryTree.(QueryTree.java:50)
at io.jmix.data.impl.jpql.QueryTree.(QueryTree.java:40)
at io.jmix.data.impl.jpql.transform.QueryTransformerAstBased.getTree(QueryTransformerAstBased.java:69)
at io.jmix.data.impl.jpql.transform.QueryTransformerAstBased.getAnalyzer(QueryTransformerAstBased.java:61)
at io.jmix.data.impl.jpql.transform.QueryTransformerAstBased.replaceOrderByExpressions(QueryTransformerAstBased.java:191)
at io.jmix.data.impl.jpql.generator.SortJpqlGenerator.transformQuery(SortJpqlGenerator.java:118)
at io.jmix.data.impl.jpql.generator.SortJpqlGenerator.processQuery(SortJpqlGenerator.java:84)
at io.jmix.data.impl.JpqlQueryBuilder.applySorting(JpqlQueryBuilder.java:227)
at io.jmix.data.impl.JpqlQueryBuilder.buildResultQuery(JpqlQueryBuilder.java:220)
at io.jmix.data.impl.JpqlQueryBuilder.getResultQueryString(JpqlQueryBuilder.java:157)
at io.jmix.data.impl.JpqlQueryBuilder.getQuery(JpqlQueryBuilder.java:170)
at io.jmix.eclipselink.impl.JpaDataStore.createQuery(JpaDataStore.java:492)
at io.jmix.eclipselink.impl.JpaDataStore.loadAll(JpaDataStore.java:166)
at io.jmix.core.datastore.AbstractDataStore.loadList(AbstractDataStore.java:127)
at io.jmix.core.impl.UnconstrainedDataManagerImpl.loadList(UnconstrainedDataManagerImpl.java:112)
at io.jmix.ui.model.impl.CollectionLoaderImpl._load(CollectionLoaderImpl.java:92)
at io.jmix.ui.model.impl.CollectionLoaderImpl.load(CollectionLoaderImpl.java:75)