Error filling report on Jasperserver , report runs on Jaspersoft Studio

Hello colleagues,


I have a problem with jasperreport server 6.0 by running a report, that uses a postgresql 9.0 connection and

the select :

select charge, avg(ic_ist_tc) as ic, 
NULL as ic_charge, messdatum  from tbl_physikal_eigensch 
where $X{NOTIN,charge, Chargennr}   and matnr = $P!{Materialnr} and $X{IN,feld,Feldstaerke} and
messdatum > CURRENT_DATE -  $P!{Zeitraum}
group by charge, messdatum 
union select charge, null as ic ,avg(ic_ist_tc ) 
as ic_charge_, messdatum  from tbl_physikal_eigensch  
where $X{IN ,charge, Chargennr}   and matnr = $P!{Materialnr} and $X{IN,feld,Feldstaerke} and
messdatum > CURRENT_DATE - $P!{Zeitraum}  group by charge, messdatum

several $X input controls :
Materialnr Einzelwert Zahl
Feldstaerke Abfrage mit Mehrfachauswahl     Select: select distinct feld from tbl_physikal_eigensch where $X{EQUAL,matnr,Materialnr} group by feld order by feld   
Zeitraum  Einzelwert Zahl
Charge Abfrage mit Mehrfachauswahl     Select: select distinct charge from tbl_physikal_eigensch where $X{EQUAL,matnr,Materialnr} group by charge order by charge
matnr - int8
feld - float8
charge - varchar


and parameters:

Materialnr : java.lang.Long
Feldstaerke: java.util.List  mit default: Arrays.asList(new Double[] {new java.lang.Double(4.0)})
Zeitraum: java.lang.Long
Chargennr: java.util.Collection

 I can run this report in jasperreport studio 6.1 without problems.

On jasperreport server I get the error message:

com.jaspersoft.jasperserver.api.JSException: Error filling report at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$FillResultListener.reportFillError( at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError( at net.sf.jasperreports.engine.fill.BaseFillHandle$ at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute( at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill( at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport( at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport( at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource( at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport( at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ at java.util.concurrent.ThreadPoolExecutor.runWorker( at java.util.concurrent.ThreadPoolExecutor$ at Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ic_charge_messdatumV8 at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource( at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource( at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource( at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource( at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters( at net.sf.jasperreports.engine.fill.JRBaseFiller.fill( at net.sf.jasperreports.engine.fill.BaseFillHandle$ ... 10 more 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: 162 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse( at org.postgresql.core.v3.QueryExecutorImpl.processResults( at org.postgresql.core.v3.QueryExecutorImpl.execute( at org.postgresql.jdbc2.AbstractJdbc2Statement.execute( at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags( at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery( at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery( at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource( ... 16 more 

Please give me a hint, which casts I should use for the parameters.
How it is possible to debug this problem. I don't know what is meant with position 162? It is within the select ?
On jasperreportStudio 6.1. the report runs without problems. Why do I get an error message on JasperReportServer after inserting the values for the 4 parameters, which are required?

Any help would be very appreciated !!



rainer.brodhagen's picture
Joined: Dec 2 2014 - 1:12am
Last seen: 2 years 4 months ago

2 Answers:

>Error executing SQL statement for : ic_charge_messdatumV8

Is Materialnr int8?

If you are using JasperReports Server 6.0, you should be using Jaspersoft Studio 6.0 to be safe and catch errors.


hozawa's picture
Joined: Apr 24 2010 - 4:31pm
Last seen: 2 years 11 months ago

Hello Hozawa,


yes , Materialnr is  int8. But in the meantime I have found the error: 

it depends on the select in the report and the Input controls on the server.
The select in the report was modified to :

select feld,charge, ic_soll, n_soll, avg(ic_ist_tc) as ic,avg(jc_ist_tc) as jc, avg(alpha) as al,avg(n) as n_,

NULL as ic_charge, NULL as jc_charge, NULL as al_charge,NULL as n_charge, messdatum  from tbl_physikal_eigensch

where $X{NOTIN,charge, Chargennr}   and matnr = $P!{Materialnr} and $X{IN,cast(feld as text),Feldstaerke} and

messdatum > CURRENT_DATE -  $P!{Zeitraum}

group by feld,charge, messdatum, ic_soll, n_soll

union select feld,charge, ic_soll, n_soll, null as ic , null as jc , NULL as al, NULL as n__,avg(ic_ist_tc )

as ic_charge_, avg(jc_ist_tc )

as jc_charge_,avg(alpha )

as al_charge_,avg(n )

as n_charge_,messdatum  from tbl_physikal_eigensch 

where $X{IN ,charge, Chargennr}   and matnr = $P!{Materialnr}  and $X{IN,cast(feld as text),Feldstaerke} and

messdatum > CURRENT_DATE - $P!{Zeitraum}  group by feld,charge, messdatum,ic_soll, n_soll

The important change is for the database field "feld" , which has a double value and the parameter Feldstaerke which is java.util.Collection. 
You have to cast this with : cast(feld as text) and therefore $X{IN,cast(feld as text),Feldstaerke}

Further you have to modify the Select in the Input control:
Old and error : select distinct feld from tbl_physikal_eigensch where $X{EQUAL,matnr,Materialnr} group by feld order by feld
New and running: select distinct cast(feld as text) from tbl_physikal_eigensch where $P{Materialnr} = matnr

Kind regards




rainer.brodhagen's picture
Joined: Dec 2 2014 - 1:12am
Last seen: 2 years 4 months ago