How to compare year in report JPQL

Support
#1

I am using following jpql for report -

select
e.date as “date”,
e.requirement as “requirement”,
complex.name as “complex.name”,
assignedTo.name as “assignedTo.name”,
e.dueDate as “dueDate”,
e.status as “status”
from sheelcompliance_ComplianceMonitoring e
left join e.complex complex
left join e.assignedTo assignedTo
where (EXTRACT(YEAR FROM e.dueDate) = EXTRACT(YEAR FROM ${dueDate1}))

where dueDate1 is a parameter of date type

I am getting following error -

An error occurred while loading data for band [ComplianceMonitorings] and query [Dataset]. Report name [Annual Compliance Monitoring Schedule]
An error occurred while loading data for data set [Dataset]
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.2-cuba): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: function pg_catalog.date_part(unknown, unknown) is not unique
  Hint: Could not choose a best candidate function. You might need to add explicit type casts.
  Position: 291
Error Code: 0
Call: SELECT t0.DATE_, t0.REQUIREMENT, t1.NAME, t2.NAME, t0.DUE_DATE, t0.STATUS FROM SHEELCOMPLIANCE_COMPLIANCE_MONITORING t0 LEFT OUTER JOIN SHEELCOMPLIANCE_COMPLEX t1 ON (t1.ID = t0.COMPLEX_ID) LEFT OUTER JOIN SEC_USER t2 ON (t2.ID = t0.ASSIGNED_TO_ID) WHERE ((EXTRACT(YEAR FROM t0.DUE_DATE) = EXTRACT(YEAR FROM ?)) AND (t0.DELETE_TS IS NULL))
	bind => [2019-08-14 00:00:00.0]
Query: ReportQuery(referenceClass=ComplianceMonitoring sql="SELECT t0.DATE_, t0.REQUIREMENT, t1.NAME, t2.NAME, t0.DUE_DATE, t0.STATUS FROM SHEELCOMPLIANCE_COMPLIANCE_MONITORING t0 LEFT OUTER JOIN SHEELCOMPLIANCE_COMPLEX t1 ON (t1.ID = t0.COMPLEX_ID) LEFT OUTER JOIN SEC_USER t2 ON (t2.ID = t0.ASSIGNED_TO_ID) WHERE ((EXTRACT(YEAR FROM t0.DUE_DATE) = EXTRACT(YEAR FROM ?)) AND (t0.DELETE_TS IS NULL))")

Internal Exception: org.postgresql.util.PSQLException: ERROR: function pg_catalog.date_part(unknown, unknown) is not unique
  Hint: Could not choose a best candidate function. You might need to add explicit type casts.
  Position: 291
Error Code: 0
Call: SELECT t0.DATE_, t0.REQUIREMENT, t1.NAME, t2.NAME, t0.DUE_DATE, t0.STATUS FROM SHEELCOMPLIANCE_COMPLIANCE_MONITORING t0 LEFT OUTER JOIN SHEELCOMPLIANCE_COMPLEX t1 ON (t1.ID = t0.COMPLEX_ID) LEFT OUTER JOIN SEC_USER t2 ON (t2.ID = t0.ASSIGNED_TO_ID) WHERE ((EXTRACT(YEAR FROM t0.DUE_DATE) = EXTRACT(YEAR FROM ?)) AND (t0.DELETE_TS IS NULL))
	bind => [2019-08-14 00:00:00.0]
Query: ReportQuery(referenceClass=ComplianceMonitoring sql="SELECT t0.DATE_, t0.REQUIREMENT, t1.NAME, t2.NAME, t0.DUE_DATE, t0.STATUS FROM SHEELCOMPLIANCE_COMPLIANCE_MONITORING t0 LEFT OUTER JOIN SHEELCOMPLIANCE_COMPLEX t1 ON (t1.ID = t0.COMPLEX_ID) LEFT OUTER JOIN SEC_USER t2 ON (t2.ID = t0.ASSIGNED_TO_ID) WHERE ((EXTRACT(YEAR FROM t0.DUE_DATE) = EXTRACT(YEAR FROM ?)) AND (t0.DELETE_TS IS NULL))")
ERROR: function pg_catalog.date_part(unknown, unknown) is not unique
  Hint: Could not choose a best candidate function. You might need to add explicit type casts.
  Position: 291

The error is in passing parameter ${dueDate1}. If i use current_date instead of parameter, the report generate without error.

How can I compare year in report add-on.

regards

Umesh

#3

Hi Umesh,
Change where clause to (EXTRACT(YEAR FROM e.dueDate) = EXTRACT(YEAR FROM CAST (${dueDate1} timestamp)))

#5

Ths doesnot solve the issue. Still gettng this error -
> An error occurred while loading data for band [ComplianceMonitorings] and query [Dataset]. Report name [Annual Compliance Monitoring Schedule]

An error occurred while loading data for data set [Dataset]
Errors found for input jpql:[select e.date , e.requirement , complex.name , assignedTo.name , e.dueDate , e.status from sheelcompliance_ComplianceMonitoring e  left join e.complex complex  left join e.assignedTo assignedTo   where (EXTRACT(YEAR FROM e.dueDate) = EXTRACT(YEAR FROM CAST (?1 timestamp)))]
CommonErrorNode [<mismatched token: [@79,232:232='=',<74>,1:232], resync=select e.date , e.requirement , complex.name , assignedTo.name , e.dueDate , e.status from sheelcompliance_ComplianceMonitoring e  left join e.complex complex  left join e.assignedTo assignedTo   where (EXTRACT(YEAR FROM e.dueDate) = EXTRACT(YEAR FROM CAST (?1 timestamp)))>]
#6

Hi,

Try to execute sample report: UserReport.zip (8.6 KB) with EXTRACT in JPQL . Will it work?

#7

Can’t download it,

Oops! That page doesn’t exist or is private.

#10

Hi,
Updated attachment link in the previous message.