Passing a parameter in IN clause of SQL

[mateen83]

My report uses the following query.

select folio_number from unit_account ua
where ua.folio_number in (1,2,3,4,5,6,7,8,9,10,11)
order by to_number(ua.folio_number)

How can i represent the IN clause in the above SQL as a parameter. If i use a string it adds a ' ' before and after and the query will not execute. How can i use an in clause so i can provide multiple account numbers in the same parameter.

[iancr01]

Try using $P!{parameter name} instead of just $P{parameter name}.

[iancr01]

You can also use:

 

select ... where $X{IN, colname, parameter name}

 

"parameter name" is a parameter of type Collection or array.

 

[mateen83]

Its worked using $P!{parameter_name} . Thanks for your help

[shatad0101]

iancr01
Wrote:

You can also use: select ... where $X{IN, colname, parameter name} "parameter name" is a parameter of type Collection or array.

 

 

Hi,

 

Today I was trying to  use parameter in where clause

such as--

 

select emp_salary,last_name

from employees

where emp_id in ($p{id1},$p{id2})

 

but i m getting error so how to

figure out this problem plz help me

 

Is it possible to do such a thing with iReport and JasperReport ?

[cbarlow3]

Yes, but your syntax is off.  Here is a line from a SQL command in one of my reports:

WHERE
  $X{ IN, NOTE_TYPE.DESCRIPTION, AlertTypeList } AND ...

This particular line is making sure that the DESCRIPTION field of a note type record (we enforce that this field is unique, as we use this description instead of a code to identify the different note types available on our system) is one of the descriptions stored in a user-selected list parameter called $P{AlertTypeList}.  With the $X{} syntax, the word "IN" is the name of the function (the other choice is "NOT IN").  It does not appear in quotation marks or with a $, etc.  The second argument is the field name.  It must be a fieldname, which is why it doesn't require $F{} around it...that would be redundant.  Similarly, the third argument must be a parameter, so you don't put $P{} around the name.

So far, so good, but what kind of parameter is this?  In your example, it looked like you had the user enter ID1 and ID2.  That's not how $X{ IN } is used.  Instead, it expects your parameter to be of type java.util.Collection or java.util.List.  These list parameters can be a bit of a pain to fill out when testing in iReport, but they can be really slick when you deploy to JasperServer and create an input control to go along with your list/collection parameter.  Specifically, when you create the input control, you can make the type of that control be either "Multi-select List of Values" if you're going to have the user choose from multiple pre-defined values or you can use "Multi-select Query" if you want to give them a multi-select selection box where the options are pulled from the database in real time.  I use the multi-select queries a lot so the user can choose from customized lists without me hardcoding choices that they are bound to modify outside my control.

Carl

[texonic2003]

cbarlow3
Wrote:
 

Yes, but your syntax is off.  Here is a line from a SQL command in one of my reports: WHERE   $X{ IN, NOTE_TYPE.DESCRIPTION, AlertTypeList } AND ... This particular line is making sure that the DESCRIPTION field of a note type record (we enforce that this field is unique, as we use this description instead of a code to identify the different note types available on our system) is one of the descriptions stored in a user-selected list parameter called $P{AlertTypeList}.  With the $X{} syntax, the word "IN" is the name of the function (the other choice is "NOT IN").  It does not appear in quotation marks or with a $, etc.  The second argument is the field name.  It must be a fieldname, which is why it doesn't require $F{} around it...that would be redundant.  Similarly, the third argument must be a parameter, so you don't put $P{} around the name. So far, so good, but what kind of parameter is this?  In your example, it looked like you had the user enter ID1 and ID2.  That's not how $X{ IN } is used.  Instead, it expects your parameter to be of type java.util.Collection or java.util.List.  These list parameters can be a bit of a pain to fill out when testing in iReport, but they can be really slick when you deploy to JasperServer and create an input control to go along with your list/collection parameter.  Specifically, when you create the input control, you can make the type of that control be either "Multi-select List of Values" if you're going to have the user choose from multiple pre-defined values or you can use "Multi-select Query" if you want to give them a multi-select selection box where the options are pulled from the database in real time.  I use the multi-select queries a lot so the user can choose from customized lists without me hardcoding choices that they are bound to modify outside my control. Carl

Code:

Hi friends,I need to check following scenario in WHERE condition.lets say I am passing 4 parameters from my application like Start_Date, EmpNo, Name, Gender. I may pass one parameter or two or else as user selected. then I need WHERE condtion as follows,if Start_date passed but others are not   then SQL quary should as.... Select * from tblemployee WHERE Start_date =$P{St_Date} & if both Start_date and EmpNO passed but others are not   then SQL quary should as.... Select * from tblemployee WHERE Start_date=$P{St_Date} AND EmpNo= $P{Emp_No}can someone help me with a sample.

[tcoultas]

I just want to say that Carl's approach worked like a charm. AND, it ran the query in an optimized manner unlike the "where FOO in $P!{variable}" syntax. Sub second return vs. 16 second return. Sweet!