When a null value is passed in as a parameter I need to change my where clause to
IS NULL
Otherwise there where clause can remain
= $P{VAR}
Can I do that with expressions? If so How?
11 Answers:
You can use a derived paramter for this, see below.
HTH,
Lucian
| Code: |
<parameter name="VarClause" isForPrompting="false">
<defaultValueExpression>$P{VAR} == null ? "IS NULL" : "= $$P{VAR}"</defaultValueExpression>
</parameter>
<queryString>... WHERE column $P!{VarClause}</queryString></td></tr></tbody></table> |
THANKS!
I got a little further...
But it thinks the var is a column name now. I am getting an error messages that says "unknown column"
So when the query is
where field = $F{VAR}
The var appears without surround single quotes, so the SQL engine things we are comparing two columns.
What exactly does the $P!{VAR} do in the query. Does the ! remove the single quotes that would normally be around string parameters?
I try to explicity put the single quotes back in to no avail.... Attached.
Post Edited by at 01/30/09 19:08
You do need to use $P!{VarClause} in the query, see here some details on this.
Regarding the quotes, did you try "= $$P{VAR}" (just as in my example)?
Regards,
Lucian
Thanks for you help.
Without the $P! it doesn't surround the value with quotes and the SQL engine thinks it's a column. So you get an error. It appears that the $P! is required
The $$ doesn't work. The error I recieve is "Syntax Error on Tokens; delete these tokens"
Attached is the latest.
Post Edited by Cory Hubert at 01/31/09 04:03
There is no attachment.
$$P would not work in the query, only in the default value expression (assuming you use a recent enough JasperReports version).
Could you try to define the parameter and query exactly like in the sample code below, and let me know how that goes? If there are any problems, add the line below to your log4j.properties and attach the output.
Regards,
Lucian
| Code: |
log4j.logger.net.sf.jasperreports.engine.query.JRJdbcQueryExecuter=debug</td></tr></tbody></table> |
I've been executing this in iReport. I am trying to get it to work there before uploading it into JasperServer.
According to iReport the syntax you specified isn't valid. So when I upload it to the JasperServer. It won't even execute.
I normally get it working in iReport before uploading it in JasperServer...
What does the $$ do? Is there any documentation on it?
BTW. I am using iReport 3.1.2 and JasperReports 3.1
I've tried
$P{Country_ISO3166} == null ? "IS NULL" : "= "+ $$P{Country_ISO3166}
As the expression used in the derivied parameter. iReport indicates that the $$ is a syntax error.
And the query is...
select
IndexName
from `Indices`
where Country_ISO3166 $P{Country_ISO3166WhereClause}
There will be single quotes around the entire where clause.
I turned on the SQLException log and the query looks like this
select Industry, Publisher, IndexName from `worldindices` where Country_ISO3166 '= AR'
when the query is changed to
When I change the query to use $P!{Country_ISO3166WhereClause} the query turns into ...
select Industry, Publisher, IndexName from `worldindices` where Country_ISO3166 = US
then query engine thinks the value is a column so I had to change the expression to surround the value with quotes.
$P{Country_ISO3166} == null ? "IS NULL" : "= '"+ $P{Country_ISO3166}+"'"
This changes the query to
select Industry, Publisher, IndexName from `worldindices` where Country_ISO3166 = 'AR'
exactly what I want.... But it returns no values...... I execute the same query in an external SQL Client and it works.
However I did find this in the logs
Tue Feb 03 12:35:27 EST 2009 INFO: Profiler Event: [SLOW QUERY] at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135) duration: 1 ms, connection-id: 120, statement-id: 16, resultset-id: 16, message: The following query was executed with a bad index, use 'EXPLAIN' for more details: select Industry, Publisher, IndexName from `worldindices` where Country_ISO3166 = 'AR'
and when I COPY and PASTED the complete SQL statement into my SQL client I get this
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select Industry, Publisher, IndexName from `worldindices` where Country_' at line 1
SQLState: 42000
ErrorCode: 1064
It appears like a bunch of extra characters have been inserted into the query somehow. The actually SQL looked fine. ONly when I executed it, I received this message. I actually manually deleted the bogus characters by delete and re-inserting the whitespace.
Any ideas????????
I think the reporting engine is somehow inserting those bad chars.
Post Edited by Cory Hubert at 02/03/09 17:51
Post Edited by Cory Hubert at 02/03/09 17:53
clhubert
Wrote:
|
I've tried $P{Country_ISO3166} == null ? "IS NULL" : "= "+ $$P{Country_ISO3166} As the expression used in the derivied parameter. iReport indicates that the $$ is a syntax error. |
Could you please try it exactly like in my sample, i.e.
$P{VAR} == null ? "IS NULL" : "= $$P{VAR}"(mind the quotes)
$$P{..} is a syntax used to escape $P{..} in report expression so that it doesn't get interpreted as a parameter placeholder. Apparently iReport is not aware of this syntax, ignore the warnings.
Regards,
Lucian
Thanks a lot.... It works now.
For some reason my jrxml didn't work with any query. Even a simple one with no expressions. It just wouldn't populate with a valid query. I winded up rebuilding the report from scratch with no expressions to get it working again. Once I got it working I put in the expressions you supplied. It works now!
However I still have the old jrxml files that don't work. I am looking at them and have no clue why it started to fall apart.
