pgsql: cannot use extract function inside a where clause

For Exmple:

 

AND (date_part('YEAR',fin_finacc_transaction.statementdate) = $P{Año})

or

AND ((extract(Year from  fin_finacc_transaction.statementdate) LIKE $P{Año}) OR $P{Año} = 'ALL')

Neither of these work, I guess it has something to do with the Parameter type, as it works when I use a number instead of the Parameter.

See error Log.

net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Cuenta de Banco
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240)
at com.jaspersoft.studio.data.jdbc.JDBCFieldsProvider.getFields(JDBCFieldsProvider.java:67)
at com.jaspersoft.studio.data.jdbc.JDBCDataAdapterDescriptor.getFields(JDBCDataAdapterDescriptor.java:75)
at com.jaspersoft.studio.property.dataset.dialog.DataQueryAdapters.doGetFields(DataQueryAdapters.java:359)
at com.jaspersoft.studio.data.designer.AQueryDesignerContainer$1.run(AQueryDesignerContainer.java:54)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: double precision = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 1419
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233)
... 5 more

Leave a Reply

Your email address will not be published. Required fields are marked *