Error filling report on Jasperserver , report runs on Jaspersoft Studio

[rainer.brodhagen]

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
Tabellenfelder:
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(EngineServiceImpl.java:1258) at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError(BaseFillHandle.java:211) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:882) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:838) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1727) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1082) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1011) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:904) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:724) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ic_charge_messdatumV8 at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1119) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:691) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:434) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:508) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:120) ... 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(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) ... 16 more 

Questions: 
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 !!

 

 

[hozawa]

>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.

 

[rainer.brodhagen]

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