LIKE keyword in Arraylist

[anubha0408]

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)
 

 

 

[luked]

Hi there,

You could try the .contains syntax. 

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

[anubha0408]

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]

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

[luked]

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

[anubha0408]

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