How mix parameter $P and $P! in query ?

[d.taillade]

Hello,

I need to mix parameter $P and $P! in query to do a single report. But I don't manage, I have an errror in query.

I have a first parameter "WITH_NUMFACT" and according to it I do :

• if 1 then numfact = param_numfact
• else tri = param_tri and another substitution

Here the query parameter (Q_WITH_NUMFACT) :

($P{WITH_NUMFACT} == 1) ? " AND FACTUR_MAITRE.NUMFACT =  $P{NUMFACT} " : " AND FACTUR_MAITRE.FLAG_TRI >= nvl( $P{TRI} ,1) $P!{Q_IN_NOTIN}"[/code]

And in my query I do :

SELECT my_columns FROM my_table WHERE 1=1 $P!{Q_WITH_NUMFACT}[/code]

If I show the parameter Q_WITH_NUMFACT, the result is :

 AND FACTUR_MAITRE.FLAG_TRI >= ((java.lang.Integer)parameter_TRI.getValue()),1)$P!{Q_IN_NOTIN}[/code]

The system doesn't translate the parameters.

How do it ?

Many thanks in advance for your help !!

[hozawa]

JasperReports doesn't support nested parameters. You can try using nested conditionals.

I just usually create JRDataSource in my program and then just passed that to instead of creating complex SQL statements in my report.

[d.taillade]

Thanks for your answer !

So I'll try with nested conditionals.

[lucianc]

What you need to do is to escape $P{..} with $$P{..} in the Q_WITH_NUMFACT expression so that they are interpreted literally:

($P{WITH_NUMFACT} == 1) ? " AND FACTUR_MAITRE.NUMFACT =  $$P{NUMFACT} " : " AND FACTUR_MAITRE.FLAG_TRI >= nvl( $$P{TRI} ,1) $P!{Q_IN_NOTIN}"[/code]

Regards,

Lucian