LIKE keyword in Arraylist

0

HI Team,

Please assist on using arraylist parameter for LIKE values. 

We have a multiselect parameter of arraylist type, and in query I would like to check if any value of arraylist is LIKE '%ABC%'. 

Please assist how to use $X{LIKE, '%ABC%',parametername}

When using the same in query its giving below error:

net.sf.jasperreports.engine.JRRuntimeException: No clause function for id LIKE found.
    at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.resolveFunction(JRAbstractQueryExecuter.java:306)
    at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.appendClauseChunk(JRAbstractQueryExecuter.java:562)
    at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.appendQueryChunk(JRAbstractQueryExecuter.java:404)
    at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.parseQuery(JRAbstractQueryExecuter.java:379)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.<init>(JRJdbcQueryExecuter.java:176)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuterFactory.createQueryExecuter(JRJdbcQueryExecuterFactory.java:207)
    at com.jaspersoft.studio.data.jdbc.JDBCFieldsProvider.getFields(JDBCFieldsProvider.java:67)
    at com.jaspersoft.studio.data.jdbc.JDBCDataAdapterDescriptor.getFields(JDBCDataAdapterDescriptor.java:70)
    at com.jaspersoft.studio.property.dataset.dialog.DataQueryAdapters.doGetFields(DataQueryAdapters.java:486)
    at com.jaspersoft.studio.data.designer.AQueryDesignerContainer$1.run(AQueryDesignerContainer.java:49)
    at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:119)
 

 

 

anubha0408's picture
Joined: Jul 6 2019 - 5:15am
Last seen: 5 days 3 hours ago

Hi Luked,

Thank you for your response. However, i am looking for something which i can use in Query itself same as using IN or NOTIN. 

Any suggestions?

Regards

Anubha

anubha0408 - 1 week 1 day ago

4 Answers:

1

Hi there,

You could try the .contains syntax. 

$X{parametername}.contains'%ABC%'

luked's picture
13
Joined: Jun 7 2018 - 3:38pm
Last seen: 18 hours 56 min ago
0

Hi Luked,

Thank you for your response. However, i am looking for something which i can use in Query itself same as using IN or NOTIN. 

Any suggestions?

Regards

Anubha

anubha0408's picture
Joined: Jul 6 2019 - 5:15am
Last seen: 5 days 3 hours ago
0

Hi Anubha,

 

I found the below which might be helpful for you

 

IN and NOTIN Clauses

JasperReports provides a special syntax to use with a where condition: the clause IN and NOTIN.

The clause is used to check whether a particular value is present in a discrete set of values. Here is an example:

SELECT * FROM ORDERS WHERE SHIPCOUNTRY IS IN ('USA','Italy','Germany')

The set here is defined by the countries USA, Italy and Germany. Assuming we are passing the set of countries in a list (or better a java.util.Collection) or in an array, the syntax to make the previous query dynamic in reference to the set of countries is:

SELECT * FROM ORDERS WHERE $X{IN, SHIPCOUNTRY, myCountries}

where myCountries is the name of the parameter that contains the set of country names. The $X{} clause recognizes three parameters:

  Type of function to apply (IN or NOTIN)
  Field name to be evaluated
  Parameter name

JasperReports handles special characters in each value. If the parameter is null or contains an empty list, meaning no value has been set for the parameter, the entire $X{} clause is evaluated as the always true statement “0 = 0”.

 

Source: http://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-us...

Using this, you can place the whole condition within a conditional parameter like;
$P{XYZ} ==1 ? $X{IN, ... , ....} : $X{NOTIN, ... , ...} ;

 

Regards

Luke

luked's picture
13
Joined: Jun 7 2018 - 3:38pm
Last seen: 18 hours 56 min ago
0

HI Luke,

NOTIN , IN are working fine in SQL query. i am looking for something to work similar to  "LIKE" in SQL for arraylst or collection type of parameter. 

Regards

Anubha

anubha0408's picture
Joined: Jul 6 2019 - 5:15am
Last seen: 5 days 3 hours ago
Feedback