how to use substring in NOTIN/IN Clause

[selvalakshmij]

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,

t2.slno,t2.item_desc,t2.qty,t2.insp_date,t2.accepted_qty,t2.rejected_qty,

t3.parameters,t3.specifications,t3.actual1,t3.actual2,t3.actual3,t3.actual4,t3.actual5

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

[narcism]

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.