parameter index out of range

[2006 IR Open Discussion]

By: Jaisankar Arunachalam - jsankar

parameter index out of range

2003-03-25 03:25

I'm using a query as following in one of my report.

 

The report is perfectly working if parameter is specified outside quotes(' ') or if i specify the value directly without parameter.

 

But I'm getting a parameter index out of range, if the parameter is specified inside quotes of the query. As per my requirement I need to specify the parameter inside quotes. Is there any solution to solve my problem?

 

The query is :

 

not working :

 

select * from employmentcrosstab(

'select 1 as rowid, b.employmentduration_desc, a.person_id from injured a full outer join employmentduration b on a.employmentduration_id=b.employmentduration_id where a.event_id=$P{event_id};')

 

working:

 

select * from employmentcrosstab(

'select 1 as rowid, b.employmentduration_desc, a.person_id from injured a full outer join employmentduration b on a.employmentduration_id=b.employmentduration_id;') where a.rowid=$P{event_id}

 

The error messages while filling the report (it is compiling without any problem) :

 

Parameter index out of range.

at org.postgresql.jdbc2.PreparedStatement.set(PreparedStatement.java:820)

at org.postgresql.jdbc2.PreparedStatement.setInt(PreparedStatement.java:201)

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:278)

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:132)

at dori.jasper.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:363)

at dori.jasper.engine.fill.JRFillSubreport.run(JRFillSubreport.java:380)

at java.lang.Thread.run(Thread.java:536)

 

NESTED BY :

Parameter index out of range.

at org.postgresql.jdbc2.PreparedStatement.set(PreparedStatement.java:820)

at org.postgresql.jdbc2.PreparedStatement.setInt(PreparedStatement.java:201)

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:278)

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:132)

at dori.jasper.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:363)

at dori.jasper.engine.fill.JRFillSubreport.run(JRFillSubreport.java:380)

at java.lang.Thread.run(Thread.java:536)

 

NESTED BY :

dori.jasper.engine.JRException: Error executing report query :

 

select * from employmentcrosstab(

'select 1 as rowid, b.employmentduration_desc, a.person_id from injured a full o

uter join employmentduration b on a.employmentduration_id=b.employmentduration_i

d where a.event_id=?;')

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:369)

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:132)

at dori.jasper.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:363)

at dori.jasper.engine.fill.JRFillSubreport.run(JRFillSubreport.java:380)

at java.lang.Thread.run(Thread.java:536)

Caused by: Parameter index out of range.

at org.postgresql.jdbc2.PreparedStatement.set(PreparedStatement.java:820)

at org.postgresql.jdbc2.PreparedStatement.setInt(PreparedStatement.java:201)

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.java:278)

 

regards

Jaisankar