Jump to content
Changes to the Jaspersoft community edition download ×

problem with parameters


Recommended Posts

I'm trying to pass a simple parameter through a query. My query reads



select CURR_CODE, VAR as VAR1, 0 VAR2 from rm_var_group_details v, rm_bu_rg_details r where v.group_type = 1 and v.var_type = 'VAR10DAY99' and v.idx_id = r.idx_id and r.risk_group_name = $P!{risk_group_name} and r.business_unit = 'ipb' and r.cob_date = (select max(rundate) from business_day where date_type = 'COB')



If I'm prompted for risk_group_name, I type TESTGROUP2, and it creates this error.




net.sf.jasperreports.engine.JRException:ÂErrorÂexecutingÂSQLÂstatementÂforÂ:ÂSwapsÂReport ÂÂÂÂatÂnet.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:121) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:673) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFillDataset.setDatasource(JRFillDataset.java:610) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFillDataset.setParameterValues(JRFillDataset.java:599) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:864) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:702) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:660) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:63) ÂÂÂÂatÂnet.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:402) ÂÂÂÂatÂnet.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:234) ÂÂÂÂatÂit.businesslogic.ireport.IReportCompiler.run(IReportCompiler.java:808) ÂÂÂÂatÂjava.lang.Thread.run(UnknownÂSource) CausedÂby:Âjava.sql.SQLException:ÂORA-00904:Â"TESTGROUP2":ÂinvalidÂidentifier  ÂÂÂÂatÂoracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) ÂÂÂÂatÂoracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) ÂÂÂÂatÂoracle.jdbc.ttc7.Oall7.receive(Oall7.java:573) ÂÂÂÂatÂoracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891) ÂÂÂÂatÂoracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830) ÂÂÂÂatÂoracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391) ÂÂÂÂatÂoracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672) ÂÂÂÂatÂoracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589) ÂÂÂÂatÂoracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527) ÂÂÂÂatÂnet.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:115) ÂÂÂÂ...Â11ÂmoreÂ

Print was not filled. Try using an EmptyDataSource...




However, if i hardcode 'TESTGROUP2' into the query the report works. Any suggestions? risk_group_name is a string.




Post edited by: ktzeng, at: 2007/01/30 19:39

Link to comment
Share on other sites

  • Replies 6
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Take the apostrophe out of the parameter identifier so that the quotes are passed into the query.



ie. your parameter identifier should be $P{risk_group_name}, not $P!{risk_group_name}



With the apostrophe included there string's quotes are stripped, so the value TESTGROUP2 in your query will be interpreted as a field name.




However, the apostrophe can be useful if you are looking for inline text matches like this:



where AFIELD = '$P!{myparametername}SOMEOTHERTEXT'






where ANOTHERFIELD like '%$P!{myotherparameter}%'




It saves having to use ugly string concatenation in SQL.

Post edited by: jmurray, at: 2007/01/30 19:57

Link to comment
Share on other sites

  • 6 months later...

First verify that $P{PARAMETER} is java.lang.string as your field.

If $P{PARAMETER} is java.lang.string the annotation

$P!{PARAMETER should be the same of $P{PARAMETER} .


But sometimes $P{PARAM} don't work properly, so I prefer to use




You can also do in this way :


define the default of $p{PARAMETER} as "'TEXFIELD'"


and then in the query write



field = $P!{PARAMETER} in the query will appear:


field = 'TEXTFIELD'


Hope that this help.




Link to comment
Share on other sites

  • 1 year later...

Hi there,

I tired all your suggestion but none worked for me.

My sql for dataset1  is

FROM table(fun_hepr_gsurv_sub_entity_resp ($P{ScheduleId},$P{Sub_Entity_Ids}))
ORDER BY entity_name, sub_entity_name, schedule_time, question

when i tried

FROM table(fun_hepr_gsurv_sub_entity_resp ('$P!{ScheduleId}','$P!{Sub_Entity_Ids}'))
ORDER BY entity_name, sub_entity_name, schedule_time, question

i get empty page,

i checked the query in database, it gives result over there.



below is my error log

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...