problem with parameters

[ktzeng]

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.

 

 

ErrorÂfillingÂprint...ÂErrorÂexecutingÂSQLÂstatementÂforÂ:ÂSwapsÂReport

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.

 

 

Thanks!

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

[jmurray]

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'

 

 

or

 

 

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

[ktzeng]

I tried that, but no results seem to be created. Any other suggestions?

[lutuanhui]

Hi :

I got the same problem as you with ireport 1.3.2.Did you solved it ? If yes,share me your solution.

Thanks

[lutuanhui]

Hi :

I got the same problem as you with ireport 1.3.2.Did you solved it ? If yes,share me your solution.

Thanks

[dpinfo]

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

 

'$P!{PARAM}'

 

You can also do in this way :

 

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

 

and then in the query write

 

where

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

 

field = 'TEXTFIELD'

 

Hope that this help.

 

Bye

dpinfo

[veerchinta]

Hi there,

I tired all your suggestion but none worked for me.

My sql for dataset1  is

SELECT *
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

SELECT *
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