Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Tuesday, December 16, 2014

Tip : Parameter with wildcard % in WHERE clause LIKE statement - Jasper Studio / iReport


This tip is useful when you use wild card characters in LIKE statement of your WHERE clasue

Example 1 : 
General SQL Query : 
SELECT *  FROM customer WHERE education LIKE '%'||'School'||' %' 
Parametrized query in iReport/Studio 
SELECT *  FROM customer where education like '%'||'$P!{Parameter1}'||' %'




Example 2 :
General SQL Query : 
SELECT *  FROM customer WHERE education LIKE '%'||'Partial'||' %'
Parametrized query in iReport/Studio 
SELECT *  FROM customer where education like '%'||'$P!{Parameter1}'||' %' 

Why $P!{} parameter instead $P{} parameter ?
Because when you use wildcards in SQL query you need to combine the parts of the actual strings.
In above example   % param %  takes part1 of String + YourEntry + part2 of String (parts could be optional).. So when you enter your entry the parameter in Studio doesn't understand.. It treats each character of your entry as separate words hence you must use multi select parameter syntax ( i.e., $P!{}) 

https://community.jaspersoft.com/questions/525243/passing-wildcards-parameter-statement

Sadakar Pochampalli
:-)