JPQL Subquery example throws error relation "table_name" does not exist, Spring Boot

Question

I am trying to implement a Subquery within a query example in Spring Boot, JPQL but getting error

2022-03-21 16:13:20.582 WARN 23580 --- [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42P01 2022-03-21 16:13:20.582 ERROR 23580 --- [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: relation "cases" does not exist Position: 30 org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) at com.sun.proxy.$Proxy464.searchCases(Unknown Source) at com.ericsson.smart.meter.smartmeter.service.Impl.CaseServiceImpl.searchCases(CaseServiceImpl.java:125) at com.ericsson.smart.meter.smartmeter.controller.CaseController.searchCases(CaseController.java:187) 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.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) at javax.servlet.http.HttpServlet.service(HttpServlet.java:681) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67) at org.hibernate.loader.Loader.getResultSet(Loader.java:2322) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037) at org.hibernate.loader.Loader.doQuery(Loader.java:956) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357) at org.hibernate.loader.Loader.doList(Loader.java:2868) at org.hibernate.loader.Loader.doList(Loader.java:2850) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682) at org.hibernate.loader.Loader.list(Loader.java:2677) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2181) at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1204) at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:177) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617) at org.hibernate.query.Query.getResultList(Query.java:165) at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:128) at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143) at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137) at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121) at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159) at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ... 63 more Caused by: org.postgresql.util.PSQLException: ERROR: relation "cases" does not exist Position: 30 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2364) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:354) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:484) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:404) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:162) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ... 94 more

My entity table structure is below:

package com.ericsson.smart.meter.smartmeter.model;
import java.util.Date;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@NoArgsConstructor
@AllArgsConstructor
@ToString
@Getter
@Setter
@Entity
@Table(name = "\"P_CC_CASE\"", schema = "public")

public class Cases {

@Id
@Column(name = "\"CASE_ID\"")
private long caseId;

@Column(name = "\"CASE_FOCUS\"")
private String caseFocus;

@Column(name = "\"CASE_TYPE\"")
private String caseType;

@Column(name = "\"CASE_CATEGORY\"")
private String caseCategory;

@Column(name = "\"CASE_RPT_CATEGORY\"")
private String caseRptCategory;

@Column(name = "\"CASE_STATUS\"")
private String caseStatus;

@Column(name = "\"CASE_CUST_SEVERITY\"")
private String caseCustSeverity;

@Column(name = "\"CASE_EON_SEVERITY\"")
private String caseeonSeverity;

@Column(name = "\"CASE_PRIORITY\"")
private String casePriority;

@Column(name = "\"CASE_ISSUE\"")
private String caseIssue;

@Column(name = "\"CREATED_ON\"")
private Date createdOn;

@Column(name = "\"EXPECTED_RESPONSE_DATE\"")
private Date expectedResponseDate;

@Column(name = "\"LAST_CONTACT_DATE\"")
private Date lastContactDate;

@Column(name = "\"FIRST_RESPONSE_TO_CUST\"")
private Date firstResponseToCust;

@Column(name = "\"CONTACT_DATE\"")
private Date contactDate;

@Column(name = "\"CREATED_BY\"")
private String createdBy;

@Column(name = "\"ORGANIZATION_ID\"")
private String organizationId;

@Column(name = "\"QUEUE\"")
private String queue;

@Column(name = "\"CHANGED_BY\"")
private String changedBy;

@Column(name = "\"EQUIPMENT_ID\"")
private String equipmentId;

@Column(name = "\"EVENT_ID\"")
private String eventId;

@Column(name = "\"CASE_TITLE\"")
private String caseTitle;

@Column(name = "\"CASE_DESC\"")
private String caseDesc;

@Column(name = "\"SUPPORT_KEY\"")
private String supportKey;

@Column(name = "\"OBJEVENTS\"")
private String objevents;

@Column(name = "\"POD\"")
private String pod;

@Column(name = "\"CHNAGED_DATE\"")
private Date chnagedDate;

}

The Query I am trying to implement is as follows:

SELECT T1.* FROM (SELECT * FROM "P_CC_CASE" WHERE "CASE_TYPE" IN ('GAS','HEAT','ELECTRICITY') OR "CASE_TYPE" IS NULL) T1;

Like: public static final String GET_CASES_BY_PARAMS = "SELECT t from (SELECT c from Cases c where c.caseType IN (:caseCategories) OR c.caseType IS NULL) t";

@Query(value = GET_CASES_BY_PARAMS, nativeQuery = true)
List searchCases(@Param("caseCategories") List caseCategories);

I am not able to understand that the inner query is also returning a set of type Cases and so does the entire query, but when writing together gives an error. Can anyone help, how can I implement this?

Thanks in advance.

JPQL Subquery example throws error relation "table_name" does not exist, Spring Boot

Answers (1)

Related Questions

JPQL Subquery example throws error relation &quot;table_name&quot; does not exist, Spring Boot

Answers (1)

Related Questions

JPQL Subquery example throws error relation "table_name" does not exist, Spring Boot