Jump to content
Changes to the Jaspersoft community edition download ×

how to use substring in NOTIN/IN Clause


Recommended Posts

I have a below query, but i am getting error as "Parameter 1 does not exist" When using substring inside IN Clause.

select t1.grn,t1.i_rep_no,t1.po_no,t1.inv_no,t1.cdate,t1.supplier,
from qc_master t1
join qc_item_list t2 on t1.i_rep_no = t2.i_rep_no
join qc_insp t3 on t2.i_rep_no = t3.i_rep_no
and t1.grn not in ('test')
and $X{IN, substr(t1.i_rep_no,1,1), type}
and cdate between $P{fromdt} and $P{todt}
and t2.rejected_qty not in ('dummy')
order by t1.i_rep_no,t2.slno
where type will be list of string, which will be given during run time .java.util.Collection
Link to comment
Share on other sites

  • Replies 1
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

You cannot use an SQL function inside a JasperReports $X clause function as per: http://jasperreports.sourceforge.net/sample.reference/query/index.html#clause_functions

Instead, you could have something like this in your SQL:

and substr(t1.i_rep_no,1,1) IN ($P!{type})

with parameter type as a java.lang.String with comma separated values, or as anything that resolves to that.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...