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

0

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}"

And in my query I do :

SELECT my_columns FROM my_table WHERE 1=1 $P!{Q_WITH_NUMFACT}

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}

The system doesn't translate the parameters.

How do it ?

Many thanks in advance for your help !!

d.taillade's picture
Joined: Nov 14 2016 - 2:44am
Last seen: 12 months 4 days ago

3 Answers:

1

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.

hozawa's picture
51078
Joined: Apr 24 2010 - 4:31pm
Last seen: 4 months 1 week ago
0

Thanks for your answer !

So I'll try with nested conditionals.

d.taillade's picture
Joined: Nov 14 2016 - 2:44am
Last seen: 12 months 4 days ago
0

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}"

Regards,

Lucian

lucianc's picture
7091
Joined: Jul 17 2006 - 1:10am
Last seen: 2 weeks 18 hours ago
Feedback
randomness