Monday, June 22, 2015

How to use IN/NOT IN Using Parameter

Example

SELECT * FROM EMPLOYEE WHERE EMP_NAME IN($P{P_STUDENT_NAME})
'SANDIP','VIJAY' <-- Not Work As Considering As A Single Value.

SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ($P!{P_STUDENT_NAME})
'SANDIP','VIJAY' <-- Work As Per Expected

SELECT * FROM ORDERS WHERE $X{IN,EMP_NAME,P_STUDENT_NAME} 
Work As Per Expected But Need To Pass Array

$P!{P_STUDENT_NAME} : This will not be treated as a SQL parameter. 
JasperReports will consider this parameter value will be a part to sql statement.
So you can also pass the query part from parameter.

Example: How to make order by as a parameterised.
select * from employee ORDER BY $P!{P_STUDENT_NAME}

Pass Parameter Value as : EMP_NAME DESC 
Note : Without " or ' sign as there is no quate in query.

No comments:

Post a Comment