How to pass parameters for IN clauses in Jasperreports?

Asked

Viewed 715 times

4

I have a question in the passage of a parameter in Ireport. I need to pass a list as a parameter.

Example: In SQL code the query looks like this:

SELECT * FROM TABELA WHERE COD IN (1,2,3,4)

In Ireport with the passage of the parameter I’m trying to do this way:

SELECT * FROM TABELA WHERE COD IN ($P{CODIGO})

And in the window parameter that opens the data (1,2,3). However, this way is not working.

java sql jasper-reports ireport

  • Related: https://answall.com/q/291236/64969

  • 1

    Even if you want to know how I do the dynamic compilation of reports independent of ireport or Jaspersoft-studio, you can open a question and mention me here for me to be notified

1 answer

3

The syntax to handle clauses IN in accordance with documentation of Jaspersoft is $X{IN, NOME_DA_COLUNA, nomeDoParametro}. In your case:

SELECT * FROM TABELA WHERE $X{IN, COD, codigos}

codigos must be a List, Collection or array.

Alternatively you can use a placeholder $P!{placeholder}. However, this solution is less desirable, among other things, because it is vulnerable to attacks from SQL Injection:

SELECT * FROM TABELA WHERE COD IN ( $P!{codigos} )

In that case, codigos is a String with comma separated codes.

  • 2

    I am against expansion strategy $P! by, in addition to the problems already mentioned, it does not work as well for the absence of values. In the expansion of the $X{IN, ...}, when there are no values, Jasper puts 1=1 instead of expression, which then maintains the integrity of the query structure. I am also against the eval unnecessary, he is more evil than the goto

  • Anthony, thanks for the answer. Come on. I’m having some doubts yet. Using WHERE $X{IN, COD, codigos} how do I create the code list in Ireport? .

  • Hi Jorge, that’s right. You create codes like any other Parameter in the sidebar (e.g., right-click and Add Parameter). The only thing you have to do for the parameter to work is to change the class of String for Collection or any of the other types suggested above.

  • Anthony, Below this part of my WHERE: AND I.CODPROD = PR.CODPROD
$X{IN, C.CODTIPOPER , TOP} 
AND C.STATUSNOTA = 'L' I’m doing it the way above and ireport is saying my code is wrong. I’m sorry to insist, but this way of passing parameters is new to me.

Browser other questions tagged java sql jasper-reports ireport

You are not signed in. Login or sign up in order to post.