Using IN in query SELECT

Hello,
I am trying to use a SELECT statement with multiple parameters (BIRT 2.3.0). 2 of the parameters need to use a where clause containing "IN". For example,

Select a, b, c, d from table
where a = ?
and b IN ?
and c IN ?
and d = ?

The values for fields "a" and "d" come from parameters with dynamic list boxes from data sets that allow only one value. I can create these with no problem.

The values for fields "b" and "c" come from parameters with dynamic list boxes from data sets. I have "allow multiple values" checked in the Edit Parameter Window.

What is the correct syntax when using "IN" in the select? Does this work differently in BIRT 2.5.1? I'm also using cascading parameters in the report - but these are not for fields "b" and "c" and these work fine.

Thanks for any advice you can offer!
Nancy

Find more posts tagged with

Comments

mwilliams

Hi Nancy,

Here's one of the devShare posts that covers one of the ways to use multi-select paramters and an IN clause in your query. Let me know if you have questions!

http://www.birt-exchange.org/org/devshare/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause/

nancyw

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="72474" data-time="1295557635" data-date="20 January 2011 - 02:07 PM">
Hi Nancy, 
 
Here's one of the devShare posts that covers one of the ways to use multi-select paramters and an IN clause in your query. Let me know if you have questions! 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause/</a> </blockquote>
 
Hi Michael, 
When I try go to this link, I get an error: 
500 - Internal server error. 
There is a problem with the resource you are looking for, and it cannot be displayed. 
 
Are you having server problems? 
 
thanks, 
nancy

mwilliams

Nancy,

I've noticed that happening on some and not on others. I've submitted a problem to the site team. Hopefully it is resolved soon.

nancyw

OK. Thanks for the quick response.
Nancy

mwilliams

It should be fixed. Are you able to see the post now?

nancyw

Hi Michael,
Thanks. I am able to get to the post. I am using the IN ('****') method in my SELECT and it works fine, but I don't know how to use this if I have more than 1 parameter that needs the IN clause - in the beforeOpen Script:

this.queryText = this.queryText.replace("****", params["pProductLines"].value.join("','" ));

If you can let me know, that would be great!

Thanks,
Nancy

mwilliams

Nancy,

If you had a second one, you could put ('yyy') in your query and replace yyy with the next parameter and so on. That should do it.

pricher

Nancy,

I think you just need one IN clause with '****' and the other with 'yyy' and apply the script twice as in:

this.queryText = this.queryText.replace("****", params["pProductLines"].value.join("','" ));
this.queryText = this.queryText.replace("yyy", params["pCountry"].value.join("','" ));

Unless I'm missing something...

P.

nancyw

Thank you P.

That's what I was looking for. But, when I run the report, if I have selected more than one value for either parameter (or both parameters), I don't get any data back - and I've checked the database. I also pasted my query into an Oracle editor (Toad) and put multiple values in each "IN" clause and I can retrieve data.

If I only select one value for each parameter, it works. I'm not getting any errors when I preview the report in BIRT (2.5.1).

Maybe I'm missing something else...

Thanks for your help,
Nancy

mwilliams

Can you paste what your query and script looks like? I will set up a sample with 2.5.1 and see if it works with the sample database.

nancyw

Thanks, Michael.

I noticed I had the double quotes in my script value.join("","")); so I changed it to value.join("','")). Now I am getting an error and don't get any results back at all:
ReportDesign (id = 1):
+ There are errors evaluating script "this.queryText = this.queryText.replace("****", params["Shop"].value.join("','"));this.queryText = this.queryText.replace("yyy", params["StatusCode"].value.join("','"));":
{1}.

Here is my script:
this.queryText = this.queryText.replace("****", params["Shop"].value.join("','"));this.queryText = this.queryText.replace("yyy", params["StatusCode"].value.join("','"));

Here is my query:
select
WO_WORKORDER,
WO_STATUS,
WO_ENTRY_DATE,
WO_DESC,
WO_TYPE,
WO_CATEGORY,
WO_FACILITY_DESC,
WO_BUILDING_DESC,
PH_PROPOSAL,
PH_PHASE,
PH_SHOP,
PH_WORK_CODE
from VW_WORKORDER
INNER JOIN VW_PHASE ON
VW_WORKORDER.WO_WORKORDER = VW_PHASE.PH_PROPOSAL
where PH_SHOP in ('****')
and WO_STATUS in ('yyy')

Thanks again for all your help and quick responses!
Nancy

mwilliams

Can you expand the '+' sign and post the entire error stack.

nancyw

Michael,
I think BIRT just needed a break. Or maybe I did.

I took out the 2nd parameter yesterday afternoon and ran it. It ran fine using only one parameter. This morning, I put the second parameter back in and it works just the way it should.

So, now I don't know what I did wrong or what I did to fix it.

Sorry you and P spent time on this.

Thanks again for your help. I really appreciate it!

Nancy

mwilliams

Nancy,

It was probably just a syntax error that was corrected when you retyped it. Glad you got it working!

delee88

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="72953" data-time="1296829655" data-date="04 February 2011 - 07:27 AM">
Nancy, 
 
It was probably just a syntax error that was corrected when you retyped it. Glad you got it working! </blockquote>
 
I have a similar problem, but a little different and wonder could you help or point me in the right direction. I have an SQL query that will be run multiple time to populate different tables. With each 
run, a different set of values will fill an IN clause of an SQL statement; 
 
The SQL statement shows WHERE wdtb_code in (?) 
 
1st run. where wdtb_code in ('a',b',c') 
2nd run. where wdtb_code in ('x',y',z') 
 
What I have been trying to do, populate the values('a',b',c' OR 'x',y',z'), using "Dataset Parameter Binding" dialog of each table. When the report run, only the first value in the IN clause, 'a', or 'x', used to produce data. What am I doing wrong? 
 
Thanks 
Dale(dalelee@rcn.com)

johnw

If you don't mind using a plugin, we released a little BIRT Helper Function Library to the Marketplace last year that will handle this with ease. The trick is you will need to put the plugin in both your development instance and your deployment instance.

http://www.birt-exchange.com/be/info/birt-functions-lib-app/

John

delee88

<blockquote class='ipsBlockquote' data-author="'johnw'" data-cid="72994" data-time="1297027414" data-date="06 February 2011 - 02:23 PM">
If you don't mind using a plugin, we released a little BIRT Helper Function Library to the Marketplace last year that will handle this with ease. The trick is you will need to put the plugin in both your development instance and your deployment instance. 
 
<a class='bbc_url' href='http://www.birt-exchange.com/be/info/birt-functions-lib-app/'>http://www.birt-exchange.com/be/info/birt-functions-lib-app/</a> 
 
John </blockquote>
 
I will take a look at the Helper functionality. Thanks. It is a know issue that placing a value like 'A','B','C' will not be processed correctly?

johnw

<blockquote class='ipsBlockquote' data-author="'delee88'" data-cid="72997" data-time="1297032053" data-date="06 February 2011 - 03:40 PM">
I will take a look at the Helper functionality. Thanks. It is a know issue that placing a value like 'A','B','C' will not be processed correctly? </blockquote>
 
It should handle it just fine. But instead of concatenating a string, make sure you just populate an array.

delee88

<blockquote class='ipsBlockquote' data-author="'johnw'" data-cid="73032" data-time="1297141917" data-date="07 February 2011 - 10:11 PM">
It should handle it just fine. But instead of concatenating a string, make sure you just populate an array. </blockquote>
 
Thanks. I am relatively new to BIRT and do not understand what you men by "populate an array" The 
sql statement I built looks like " and id in (?)". IN the Dataset Parameter Binding dialog, I am entering, 
'a','b','c'. How would I build the array and use it it the sql? 
 
Thanks for your feedback