In clause in query

HI,
I am very new in birt. Please tell me how to write a query with In clause in birt and how to pass parameter with comma separated values.

for example:
Select emp_name , dept, age from emp where emp_name IN ('Ashish','Amit','john')

Find more posts tagged with

Comments

jhelbling

Hello ashish19

After the creation of your dataSource/dataSet, you should be able to modify the query to add your IN clause.

The sample you wrote (.... where emp_name IN ('Ashish','Amit','john') ) should work.

Did you get any error ? Or don't you get any data when running your query ?

Julien

ashish19

thanks Julien
I want to pass comma seperated values in IN clause.
my query is

select
subject.IDSUBJECT as subjectid,subject.firstname as fname,subject.middlename as mname,
subject.LASTNAME as lname,subjecttype.name as subjecttypename, subject.FATHERNAME, subject.dob,
subject.gender, location.ID as locationid , location.name as locationname

from subject , subjecttype, location , session_enrlogging where subject.IDSCHOOL = location.ID and
subject.SUBJECTTYPECODE = subjecttype.SUBJECTTYPECODE and subject.IDSUBJECT IN(?)
and session_enrlogging.sessionid=? and session_enrlogging.isactive = 1

the ? does not accept the coma separated values .

jhelbling

I think that the error comes because a prepared statement take only one value for each '?'. So you would have to create a query like : ..... subject.IDSUBJECT IN(?,?,?,?,?)

But this would mean you have to know how many value you will always have ...

Another solution could be to use a script in order to complete your request before submitting it. When you select your dataset, on the script tab you can write some code in the 'beforeOpen' section. Here is a code sample :

this.queryText = this.queryText + "where CLASSICMODELS.CUSTOMERS.CUSTOMERNAME IN ('" + (params["p1"]) + "')";

In your case you will probably have to manipulate the params["p1"] in order to correctly create you string.

Hope it helps,
Julien

jhelbling

And just to complete what I wrote before, you can find additionnal information/example here :<br />
<br />
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/338-birt-parameters-using-in-clause/#description'>BIRT Parameters - using IN clause - Designs & Code - BIRT Exchange</a><br />
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/688-using-a-multivalue-parameter-in-an-in-clause/#description'>Using a multivalue parameter in an in clause - Designs & Code - BIRT Exchange</a><br />
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause/#description'>Using a Multivalue parameter in a in clause - Designs & Code - BIRT Exchange</a>