SQL Query returning column 'id' not found in JPA-CodePudding

@Query(value = "select re.id,re.external_ref_no ,re.tr_date ,re.event_start_time ,re.event_end_time ,re.status ,\n"  
        "re.event_data_link ,re.created_at ,re.created_by ,re.last_updated_at ,re.last_updated_by  \n"  
        "from  report_master rm join report_event re on rm.id=re.report_id join event_report_mapping_ erm on re.id =erm.event_id "  
        "   where re.report_id =?1 and re.external_ref_no =?2 and re.tr_date =?3 and re.service_name=?4", nativeQuery = true)
ReportEventModel findEventByExternalRefNoAndTrDate1(Long reportId, String externalRefNo, String trDate
        , String serviceName);

2022-09-12 14:26:45.029 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] extracted value ([name4_5_] : [VARCHAR]) - [HOURLY-EARLY-PAYIN1] 2022-09-12 14:26:45.033 DEBUG (qtp952590825-41) trkId=123123132123 [org.hibernate.SQL] select re.id, re.external_ref_no , re.tr_date , re.event_start_time , re.event_end_time , re.status , re.event_data_link , re.created_at , re.created_by , re.last_updated_at , re.last_updated_by
from report_master rm join report_event re on rm.id=re.report_id join event_report_mapping_ erm on re.id =erm.event_id
where re.report_id =? and re.external_ref_no =? and re.tr_date =? and re.service_name=? 2022-09-12 14:26:45.037 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicBinder] binding parameter [1] as [BIGINT] - [61] 2022-09-12 14:26:45.039 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicBinder] binding parameter [2] as [VARCHAR] - [8a34c] 2022-09-12 14:26:45.039 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicBinder] binding parameter [3] as [VARCHAR] - [2021-12-24] 2022-09-12 14:26:45.039 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicBinder] binding parameter [4] as [VARCHAR] - [BOGS] 2022-09-12 14:26:45.042 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] extracted value ([id] : [BIGINT]) - [50] 2022-09-12 14:26:45.047 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] extracted value ([created_at] : [TIMESTAMP]) - [2022-09-12 14:25:58.0] 2022-09-12 14:26:45.048 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] extracted value ([created_by] : [VARCHAR]) - [Manish] 2022-09-12 14:26:45.048 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] extracted value ([event_data_link] : [VARCHAR]) - [HOURLY-EARLY-PAYIN1/2021-12-24/8a34c] 2022-09-12 14:26:45.048 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] extracted value ([external_ref_no] : [VARCHAR]) - [8a34c] 2022-09-12 14:26:45.050 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] extracted value ([last_updated_at] : [VARCHAR]) - [2022-09-12 14:25:58] 2022-09-12 14:26:45.050 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] extracted value ([last_updated_by] : [VARCHAR]) - [Manish] 2022-09-12 14:26:45.050 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] extracted value ([event_end_time] : [TIMESTAMP]) - [null] 2022-09-12 14:26:45.056 WARN (qtp952590825-41) trkId=123123132123 [o.h.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0, SQLState: S0022 2022-09-12 14:26:45.056 ERROR (qtp952590825-41) trkId=123123132123 [o.h.engine.jdbc.spi.SqlExceptionHelper] Column 'report_id' not found. 2022-09-12 14:26:45.078 ERROR (qtp952590825-41) trkId=123123132123 [c.u.b.server.advisor.ExceptionAdvisor] Exception occurred.

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select re.id,re.external_ref_no ,re.tr_date ,re.event_start_time ,re.event_end_time ,re.status , re.event_data_link ,re.created_at ,re.created_by ,re.last_updated_at ,re.last_updated_by
from report_master rm join report_event re on rm.id=re.report_id join event_report_mapping_ erm on re.id =erm.event_id where re.report_id =? and re.external_ref_no =? and re.tr_date =? and re.service_name=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:281) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)

CodePudding user response：

It seems like all data are not properly mapped. As seen in logs data is also retrieved from the database. Add report_id in the Select clause of SQL Query and try. Try Using this:

"select re.id,re.external_ref_no ,re.tr_date ,re.event_start_time ,re.event_end_time ,re.status ,\n"  
    "re.event_data_link ,re.created_at ,re.created_by ,re.last_updated_at ,re.last_updated_by, re.report_id  \n"  
    "from report_event re join report_master rm on rm.id=re.report_id join event_report_mapping_ erm on re.id =erm.event_id "  
    "   where re.report_id =?1 and re.external_ref_no =?2 and re.tr_date =?3 and re.service_name=?4"

CodePudding user response：

SQL is unable to find report_id in report_event table.

May be it is report_event_id ?