Eclipse Community Forums: BIRT » Creating a sql select with an IN clause

Home » Archived » BIRT » Creating a sql select with an IN clause

Creating a sql select with an IN clause [message #210437]

Wed, 10 January 2007 14:21

Eclipse User

I have numerous reports that use a list of values that are inserted into
a sql IN clause. Example 'select a, b from c where d IN (e, f, g)'

I know that BIRT does not currently support passing multiple values in a
single parameter. I also want to use the viewer due to it's
functionality. Is there any way to use session parameters in the select
statement? My thinking is that I can do something like:

session.setAttribute("values", "e, f, g");

and then forward to /frameset?__report=report.rptdesign

where the report design would insert the values of session attribute
'values' into the select statement. Something like 'select a, b from c
where d IN (' + params["values"] + ')'.

Is this even possible? And if so, what is the correct way to access
session attributes in the select statement?

Re: Creating a sql select with an IN clause [message #210932 is a reply to message #210437]

Thu, 11 January 2007 15:27

Eclipse User

Is someone looking into this or should I just give up and use iReports?

Ed Rouse wrote:
> I have numerous reports that use a list of values that are inserted into
> a sql IN clause. Example 'select a, b from c where d IN (e, f, g)'
>
> I know that BIRT does not currently support passing multiple values in a
> single parameter. I also want to use the viewer due to it's
> functionality. Is there any way to use session parameters in the select
> statement? My thinking is that I can do something like:
>
> session.setAttribute("values", "e, f, g");
>
> and then forward to /frameset?__report=report.rptdesign
>
> where the report design would insert the values of session attribute
> 'values' into the select statement. Something like 'select a, b from c
> where d IN (' + params["values"] + ')'.
>
> Is this even possible? And if so, what is the correct way to access
> session attributes in the select statement?

Re: Creating a sql select with an IN clause [message #210940 is a reply to message #210932]

Thu, 11 January 2007 15:39

Eclipse User

Originally posted by: xxx.xxx.com

Hello Ed,

Same frustration here :(, anyways I am just wondering how would you modify
a report in first place to use the parameter binding on a per request
basis, I mean how would you sat qry = qry + "params["xyz"]" , if you
could do that it would be great, in any case you still will have to use a
custom viewer.

Thanks.

Re: Creating a sql select with an IN clause [message #211047 is a reply to message #210437]

Thu, 11 January 2007 23:52

Eclipse User

Originally posted by: JasonWeathersby.alltel.net

This is possible. You can modify the entire sql in property binding or in
the beforeopen script.
Take a look at
http://www.eclipse.org/birt/phoenix/examples/reports/birt2.1 /querymod/index.php

So you could put your whole in clause in the parameter or make your
parameter have just the 3 values. Currently we do not support multiple
selects in the list box.

Jason

Re: Creating a sql select with an IN clause [message #211055 is a reply to message #210940]

Thu, 11 January 2007 23:54

Eclipse User

Originally posted by: JasonWeathersby.alltel.net

You can do exactly that. Take a look at the example I posted.
Also using script you can retrieve values from session.
look here and search for session.
http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp

Jason

Re: Creating a sql select with an IN clause [message #211269 is a reply to message #211055]

Fri, 12 January 2007 11:40

Eclipse User

Originally posted by: jonathan.kyme.xxxx.xom

Thanks for the response Jason,

I I use a custom parameter screen and get the parameters from a custom JSP
how do I make sure that the parameters I get from the JSP are populated
into the the "params" array , so that I can access it like params["xyz"] ,
and the parameter xyz is not coming from the BIRT web viewer but a custom
JSP.

Please advise...

Re: Creating a sql select with an IN clause [message #211277 is a reply to message #211055]

Fri, 12 January 2007 13:08

Eclipse User

Thanks so much. This and the previous were exactly what I was looking for.

Jason Weathersby wrote:
> You can do exactly that. Take a look at the example I posted.
> Also using script you can retrieve values from session.
> look here and search for session.
> http://www.eclipse.org/birt/phoenix/deploy/reportScripting.p hp
>
> Jason
>
>

Re: Creating a sql select with an IN clause [message #211284 is a reply to message #211047]

Fri, 12 January 2007 14:25

Eclipse User

I followed this example and it works up to a point. The problem I ran
into is that when I add "where a IN ('b', 'c')" to the select, it only
return those rows where a = b. If I switch the clause to read "where a
IN ('c', 'b')", it only returns where a = c. It seems to be matching the
first value, but none of the subsequent values. The same query, run from
my current code works fine.

Any thoughts on why this isn't returning the entire data set?

Jason Weathersby wrote:
> This is possible. You can modify the entire sql in property binding or
> in the beforeopen script.
> Take a look at
> http://www.eclipse.org/birt/phoenix/examples/reports/birt2.1 /querymod/index.php
>
>
> So you could put your whole in clause in the parameter or make your
> parameter have just the 3 values. Currently we do not support multiple
> selects in the list box.
>
> Jason
>
>

Re: Creating a sql select with an IN clause [message #211312 is a reply to message #211277]

Fri, 12 January 2007 15:06

Eclipse User

Originally posted by: jonathan.kyme.xxx.com

Hello Ed,

Glad to know that you got it working, how are you passing those multiple
values "b,c" do you use seperate textboxes for those values in webviewer
or do you a custom parameter screen?

If you use a custom parameter screen and allow multiple values to be
selected from a listbox , how would you make sure that parametr values
from a custom JSP map to the "params" so as to be accessed in parameter
binding or scripting later on?

Thanks,
Jonathan.

Re: Creating a sql select with an IN clause [message #211352 is a reply to message #211284]

Fri, 12 January 2007 16:54

Eclipse User

Take a look at this example.
This runs against the sample db and uses property binding n the dataset with
where clause parameter.

Jason

<?xml version="1.0" encoding="UTF-8"?>



<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.6"
id="1">

<property name="createdBy">Eclipse BIRT Designer Version
2.1.1.v20060922-1058 Build <20060926-0959></property>

<property name="units">in</property>

<property name="comments">Copyright (c) 2006 <<Your Company Name
here>></property>

<list-property name="propertyBindings">

<structure>

<property name="name">queryText</property>

<property name="id">5</property>

<expression name="value">"Select * from orderdetails " +
params["whereClause"]</expression>

</structure>

<structure>

<property name="name">queryTimeOut</property>

<property name="id">5</property>

</structure>

</list-property>

<parameters>

<scalar-parameter name="whereClause" id="6">

<property name="valueType">static</property>

<property name="dataType">string</property>

<property name="controlType">text-box</property>

<property name="defaultValue">where PRODUCTCODE IN ('S18_1749', 'S18_2248',
'S18_4409')</property>

<structure name="format">

<property name="category">Unformatted</property>

</structure>

</scalar-parameter>

</parameters>

<data-sources>

<oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc"
name="Data Source" id="4">

<text-property name="displayName"></text-property>

<property
name="odaDriverClass">org.eclipse.birt.report.data.oda.sampledb.Driver </property>

<property name="odaURL">jdbc:classicmodels:sampledb</property>

<property name="odaUser">ClassicModels</property>

<encrypted-property name="odaPassword"></encrypted-property>

</oda-data-source>

</data-sources>

<data-sets>

<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="Data Set" id="5">

<structure name="cachedMetaData">

<list-property name="resultSet">

<structure>

<property name="position">1</property>

<property name="name">ORDERNUMBER</property>

<property name="dataType">integer</property>

</structure>

<structure>

<property name="position">2</property>

<property name="name">PRODUCTCODE</property>

<property name="dataType">string</property>

</structure>

<structure>

<property name="position">3</property>

<property name="name">QUANTITYORDERED</property>

<property name="dataType">integer</property>

</structure>

<structure>

<property name="position">4</property>

<property name="name">PRICEEACH</property>

<property name="dataType">float</property>

</structure>

<structure>

<property name="position">5</property>

<property name="name">ORDERLINENUMBER</property>

<property name="dataType">integer</property>

</structure>

</list-property>

</structure>

<property name="dataSource">Data Source</property>

<list-property name="resultSet">

<structure>

<property name="position">1</property>

<property name="name">ORDERNUMBER</property>

<property name="nativeName">ORDERNUMBER</property>

<property name="dataType">integer</property>

<property name="nativeDataType">4</property>

</structure>

<structure>

<property name="position">2</property>

<property name="name">PRODUCTCODE</property>

<property name="nativeName">PRODUCTCODE</property>

<property name="dataType">string</property>

<property name="nativeDataType">12</property>

</structure>

<structure>

<property name="position">3</property>

<property name="name">QUANTITYORDERED</property>

<property name="nativeName">QUANTITYORDERED</property>

<property name="dataType">integer</property>

<property name="nativeDataType">4</property>

</structure>

<structure>

<property name="position">4</property>

<property name="name">PRICEEACH</property>

<property name="nativeName">PRICEEACH</property>

<property name="dataType">float</property>

<property name="nativeDataType">8</property>

</structure>

<structure>

<property name="position">5</property>

<property name="name">ORDERLINENUMBER</property>

<property name="nativeName">ORDERLINENUMBER</property>

<property name="dataType">integer</property>

<property name="nativeDataType">5</property>

</structure>

</list-property>

<property name="queryText">select *

from orderdetails

</property>

<xml-property name="designerValues"><![CDATA[<?xml version="1.0"
encoding="UTF-8"?>

<model:DesignValues
xmlns:design="http://www.eclipse.org/datatools/connectivity/oda/design"
xmlns:model="http://www.eclipse.org/birt/report/model/adapter/odaModel">

<Version>1.0</Version>

<design:ResultSets derivedMetaData="true">

<design:resultSetDefinitions>

<design:resultSetColumns>

<design:resultColumnDefinitions>

<design:attributes>

<design:name>ORDERNUMBER</design:name>

<design:position>1</design:position>

<design:nativeDataTypeCode>4</design:nativeDataTypeCode>

<design:precision>10</design:precision>

<design:scale>0</design:scale>

<design:nullability>Nullable</design:nullability>

</design:attributes>

<design:usageHints>

<design:label>ORDERNUMBER</design:label>

<design:formattingHints>

<design:displaySize>11</design:displaySize>

</design:formattingHints>

</design:usageHints>

</design:resultColumnDefinitions>

<design:resultColumnDefinitions>

<design:attributes>

<design:name>PRODUCTCODE</design:name>

<design:position>2</design:position>

<design:nativeDataTypeCode>12</design:nativeDataTypeCode>

<design:precision>15</design:precision>

<design:scale>0</design:scale>

<design:nullability>Nullable</design:nullability>

</design:attributes>

<design:usageHints>

<design:label>PRODUCTCODE</design:label>

<design:formattingHints>

<design:displaySize>15</design:displaySize>

</design:formattingHints>

</design:usageHints>

</design:resultColumnDefinitions>

<design:resultColumnDefinitions>

<design:attributes>

<design:name>QUANTITYORDERED</design:name>

<design:position>3</design:position>

<design:nativeDataTypeCode>4</design:nativeDataTypeCode>

<design:precision>10</design:precision>

<design:scale>0</design:scale>

<design:nullability>Nullable</design:nullability>

</design:attributes>

<design:usageHints>

<design:label>QUANTITYORDERED</design:label>

<design:formattingHints>

<design:displaySize>11</design:displaySize>

</design:formattingHints>

</design:usageHints>

</design:resultColumnDefinitions>

<design:resultColumnDefinitions>

<design:attributes>

<design:name>PRICEEACH</design:name>

<design:position>4</design:position>

<design:nativeDataTypeCode>8</design:nativeDataTypeCode>

<design:precision>15</design:precision>

<design:scale>0</design:scale>

<design:nullability>Nullable</design:nullability>

</design:attributes>

<design:usageHints>

<design:label>PRICEEACH</design:label>

<design:formattingHints>

<design:displaySize>22</design:displaySize>

</design:formattingHints>

</design:usageHints>

</design:resultColumnDefinitions>

<design:resultColumnDefinitions>

<design:attributes>

<design:name>ORDERLINENUMBER</design:name>

<design:position>5</design:position>

<design:nativeDataTypeCode>5</design:nativeDataTypeCode>

<design:precision>5</design:precision>

<design:scale>0</design:scale>

<design:nullability>Nullable</design:nullability>

</design:attributes>

<design:usageHints>

<design:label>ORDERLINENUMBER</design:label>

<design:formattingHints>

<design:displaySize>6</design:displaySize>

</design:formattingHints>

</design:usageHints>

</design:resultColumnDefinitions>

</design:resultSetColumns>

</design:resultSetDefinitions>

<design:resultSetDefinitions>

<design:resultSetColumns>

<design:resultColumnDefinitions>

<design:attributes>

<design:name>ORDERNUMBER</design:name>

<design:position>1</design:position>

<design:nativeDataTypeCode>4</design:nativeDataTypeCode>

</design:attributes>

</design:resultColumnDefinitions>

<design:resultColumnDefinitions>

<design:attributes>

<design:name>PRODUCTCODE</design:name>

<design:position>2</design:position>

<design:nativeDataTypeCode>12</design:nativeDataTypeCode>

</design:attributes>

</design:resultColumnDefinitions>

<design:resultColumnDefinitions>

<design:attributes>

<design:name>QUANTITYORDERED</design:name>

<design:position>3</design:position>

<design:nativeDataTypeCode>4</design:nativeDataTypeCode>

</design:attributes>

</design:resultColumnDefinitions>

<design:resultColumnDefinitions>

<design:attributes>

<design:name>PRICEEACH</design:name>

<design:position>4</design:position>

<design:nativeDataTypeCode>8</design:nativeDataTypeCode>

</design:attributes>

</design:resultColumnDefinitions>

<design:resultColumnDefinitions>

<design:attributes>

<design:name>ORDERLINENUMBER</design:name>

<design:position>5</design:position>

<design:nativeDataTypeCode>5</design:nativeDataTypeCode>

</design:attributes>

</design:resultColumnDefinitions>

</design:resultSetColumns>

</design:resultSetDefinitions>

</design:ResultSets>

</model:DesignValues>]]></xml-property>

</oda-data-set>

</data-sets>

<page-setup>

<simple-master-page name="Simple MasterPage" id="2">

<page-footer>

<text id="3">

<property name="contentType">html</property>

<text-property name="content"><![CDATA[<value-of>new
Date()</value-of>]]></text-property>

</text>

</page-footer>

</simple-master-page>

</page-setup>

<body>

<table id="7">

<property name="width">100%</property>

<property name="dataSet">Data Set</property>

<list-property name="boundDataColumns">

<structure>

<property name="name">ORDERNUMBER</property>

<expression name="expression">dataSetRow["ORDERNUMBER"]</expression >

<property name="dataType">integer</property>

</structure>

<structure>

<property name="name">PRODUCTCODE</property>

<expression name="expression">dataSetRow["PRODUCTCODE"]</expression >

<property name="dataType">string</property>

</structure>

<structure>

<property name="name">QUANTITYORDERED</property>

<expression name="expression">dataSetRow["QUANTITYORDERED"]</expression >

<property name="dataType">integer</property>

</structure>

<structure>

<property name="name">PRICEEACH</property>

<expression name="expression">dataSetRow["PRICEEACH"]</expression>

<property name="dataType">float</property>

</structure>

<structure>

<property name="name">ORDERLINENUMBER</property>

<expression name="expression">dataSetRow["ORDERLINENUMBER"]</expression >

<property name="dataType">integer</property>

</structure>

</list-property>

<column id="36"/>

<column id="37"/>

<column id="38"/>

<column id="39"/>

<column id="40"/>

<header>

<row id="8">

<cell id="9">

<label id="10">

<text-property name="text">ORDERNUMBER</text-property>

</label>

</cell>

<cell id="11">

<label id="12">

<text-property name="text">PRODUCTCODE</text-property>

</label>

</cell>

<cell id="13">

<label id="14">

<text-property name="text">QUANTITYORDERED</text-property>

</label>

</cell>

<cell id="15">

<label id="16">

<text-property name="text">PRICEEACH</text-property>

</label>

</cell>

<cell id="17">

<label id="18">

<text-property name="text">ORDERLINENUMBER</text-property>

</label>

</cell>

</row>

</header>

<detail>

<row id="19">

<cell id="20">

<data id="21">

<property name="resultSetColumn">ORDERNUMBER</property>

</data>

</cell>

<cell id="22">

<data id="23">

<property name="resultSetColumn">PRODUCTCODE</property>

</data>

</cell>

<cell id="24">

<data id="25">

<property name="resultSetColumn">QUANTITYORDERED</property>

</data>

</cell>

<cell id="26">

<data id="27">

<property name="resultSetColumn">PRICEEACH</property>

</data>

</cell>

<cell id="28">

<data id="29">

<property name="resultSetColumn">ORDERLINENUMBER</property>

</data>

</cell>

</row>

</detail>

<footer>

<row id="30">

<cell id="31"/>

<cell id="32"/>

<cell id="33"/>

<cell id="34"/>

<cell id="35"/>

</row>

</footer>

</table>

</body>

</report>

"Ed Rouse" <erouse@comsquared.com> wrote in message
news:eo8nb2$e39$1@utils.eclipse.org...
>I followed this example and it works up to a point. The problem I ran into
>is that when I add "where a IN ('b', 'c')" to the select, it only return
>those rows where a = b. If I switch the clause to read "where a IN ('c',
>'b')", it only returns where a = c. It seems to be matching the first
>value, but none of the subsequent values. The same query, run from my
>current code works fine.
>
> Any thoughts on why this isn't returning the entire data set?
>
> Jason Weathersby wrote:
>> This is possible. You can modify the entire sql in property binding or
>> in the beforeopen script.
>> Take a look at
>> http://www.eclipse.org/birt/phoenix/examples/reports/birt2.1 /querymod/index.php
>> So you could put your whole in clause in the parameter or make your
>> parameter have just the 3 values. Currently we do not support multiple
>> selects in the list box.
>>
>> Jason
>>

Re: Creating a sql select with an IN clause [message #211384 is a reply to message #211352]

Fri, 12 January 2007 17:53

Eclipse User

Originally posted by: jonatha.xxxx.com

Thanks for the response Jason,

I I use a custom parameter screen and get the parameters from a custom JSP
how do I make sure that the parameters I get from the JSP are populated
into the the "params" array , so that I can access it like params["xyz"] ,
and the parameter xyz is not coming from the BIRT web viewer but a custom
JSP.

Please advise...

Re: Creating a sql select with an IN clause [message #211584 is a reply to message #211384]

Mon, 15 January 2007 11:13

Eclipse User

How are you calling the webviewer now?

Jason

"Jonathan Kyme" <jonatha@xxxx.com> wrote in message
news:d911a0bfb8ecd4d5a1cc6617bfa2a588$1@www.eclipse.org...
> Thanks for the response Jason,
>
> I I use a custom parameter screen and get the parameters from a custom JSP
> how do I make sure that the parameters I get from the JSP are populated
> into the the "params" array , so that I can access it like params["xyz"] ,
> and the parameter xyz is not coming from the BIRT web viewer but a custom
> JSP.
>
> Please advise...
>
>

Re: Creating a sql select with an IN clause [message #211741 is a reply to message #211584]

Tue, 16 January 2007 11:01

Eclipse User

Originally posted by: jonathan.kyme.xxx.com

Hello Jason,

Is it possible to pass the parameters as key value pair in the get method
call to the WebViewer Servlet and those parameters being accesses by the
scripting or parameter binding later in the report design.

Please advise..

Thanks.

Re: Creating a sql select with an IN clause [message #211774 is a reply to message #211741]

Tue, 16 January 2007 11:39

Eclipse User

Yes. If the parameter is passed in the URL using parameter=value this will
put the parameter value in the params array, which will allow you to bind
this in property binding or script. You could also retrieve values from
session.

Jason

"Jonathan Kyme" <jonathan.kyme@xxx.com> wrote in message
news:401fe6836bcf6d93d2a430751b874bc1$1@www.eclipse.org...
> Hello Jason,
>
> Is it possible to pass the parameters as key value pair in the get method
> call to the WebViewer Servlet and those parameters being accesses by the
> scripting or parameter binding later in the report design.
>
> Please advise..
>
> Thanks.
>

Re: Creating a sql select with an IN clause [message #213627 is a reply to message #211774]

Wed, 24 January 2007 05:55

Eclipse User

Hello ,
I m also using custom parameter screen to pass parameters. Now ther was a
need to pass mutiple values to a single parameter.
How can I pass them as a single parameter? Also what is the format of
default values .
Plz guide
Thanks
Mathi

Re: Creating a sql select with an IN clause [message #213726 is a reply to message #213627]

Wed, 24 January 2007 12:01

Eclipse User

If you want to pass multiple parameters in one parameter you can put them in
a string parmater.
In script or binding you will then have to parse that string to use the
individual parameter.
Can you give more detail of what you are trying to do?

Jason

"Mathi" <kavi_mathi@yahoo.co.in> wrote in message
news:fac1a7f41dd7adc1883ddbccc14dd6de$1@www.eclipse.org...
> Hello ,
> I m also using custom parameter screen to pass parameters. Now ther was a
> need to pass mutiple values to a single parameter. How can I pass them as
> a single parameter? Also what is the format of default values .
> Plz guide
> Thanks
> Mathi
>
>

Re: Creating a sql select with an IN clause [message #213903 is a reply to message #213726]

Thu, 25 January 2007 00:38

Eclipse User

Hello Jason,
The sql used is

select
item_feature.item_id
from
item_feature,
service_model,
item
where
item_feature.item_id = item.item_id and
item_feature.service_model_id = service_model.service_model_id and
service_model.sub_service_id in(17,15,16) and
group by
item_feature.item_id
having
count(item_feature.item_id) > 2

Here instead of 17,15,16 I want to pass a prameter . And more over in the
parameter the number of values are not fixed. It may be 18,19,20,25..
also . How can I do this?

Thanks
Regards
Mathi

Re: Creating a sql select with an IN clause [message #214111 is a reply to message #213903]

Thu, 25 January 2007 11:22

Eclipse User

You can add a string parameter that has a value of 17, 15, 16 .... and
modify the queryText using property binding
or script. Take a look at this example video.
http://www.eclipse.org/birt/phoenix/examples/reports/birt2.1 /querymod/index.php

Jason

"Mathi" <kavi_mathi@yahoo.co.in> wrote in message
news:75288128e5ec45bd373ff207b2f8b128$1@www.eclipse.org...
> Hello Jason,
> The sql used is
> select item_feature.item_id
> from
> item_feature,
> service_model,
> item
> where item_feature.item_id = item.item_id and
> item_feature.service_model_id = service_model.service_model_id and
> service_model.sub_service_id in(17,15,16) and
> group by item_feature.item_id
> having count(item_feature.item_id) > 2
>
>
> Here instead of 17,15,16 I want to pass a prameter . And more over in the
> parameter the number of values are not fixed. It may be 18,19,20,25..
> also . How can I do this?
>
>
> Thanks
> Regards
> Mathi
>

Previous Topic:	Using JNDI to obtain db connection
Next Topic:	No chart in the Report viewer

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Jul 15 11:12:47 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter