Eclipse Community Forums: BIRT » Nested Query as Parameter

Home » Archived » BIRT » Nested Query as Parameter

Nested Query as Parameter [message #73548]

Tue, 13 September 2005 11:09

Eclipse User

Can I use a nested query as a parameter in BIRT?

I created a Data Set with the following SQL Query:

SELECT FirstName
FROM Users
WHERE (Users.Firstname IN (?))

Then I set param1 to be a String with a default value of:

"SELECT Users.FirstName FROM Users WHERE Users.FirstName like 'm%'"

I know this is a silly query when you add the parameter in place of the ?,
but this is just a simplified example of the functionality I need. I would
expect this to return all users who's first name starts with the letter 'm',
but I get no results when I click Preview Results or run some report with
this dataset.

Can I use parameters in this way? Am I just misusing my quotation marks or
something like that?

Thanks for the info!

--Floor

Re: Nested Query as Parameter [message #73978 is a reply to message #73548]

Wed, 14 September 2005 01:39

Eclipse User

Floor,

If you build a query say
select * from customers
Set no dataset parameters, but create a report parameter
like
ParamSubSelect type String and then set its default value to something like
"(Select customerNumber from customers where customers.customerName like
'%Z%')"

Then edit the beforeOpen method of the dataset and set queryText to the
following:
queryText ="select * from customers where (Customers.customerNumber IN " +
params["ParamSubSelect"] + ")";

This should do what you are looking for.
One caveat is that you will get exception in the designer when you first
open the report, but the report will run. I am looking into why the error
occurs.

Jason Weathersby
BIRT PMC

"floor" <floorflux@hotmail.com> wrote in message
news:dg6q4g$sio$1@news.eclipse.org...
> Can I use a nested query as a parameter in BIRT?
>
> I created a Data Set with the following SQL Query:
>
> SELECT FirstName
> FROM Users
> WHERE (Users.Firstname IN (?))
>
> Then I set param1 to be a String with a default value of:
>
> "SELECT Users.FirstName FROM Users WHERE Users.FirstName like 'm%'"
>
> I know this is a silly query when you add the parameter in place of the ?,
> but this is just a simplified example of the functionality I need. I
> would expect this to return all users who's first name starts with the
> letter 'm', but I get no results when I click Preview Results or run some
> report with this dataset.
>
> Can I use parameters in this way? Am I just misusing my quotation marks
> or something like that?
>
> Thanks for the info!
>
> --Floor
>
>
>
>

Re: Nested Query as Parameter [message #74620 is a reply to message #73978]

Thu, 15 September 2005 16:12

Eclipse User

That works great! Thanks a ton!

--Floor

"Jason Weathersby" <jweathersby@actuate.com> wrote in message
news:dg9net$7br$1@news.eclipse.org...
> Floor,
>
> If you build a query say
> select * from customers
> Set no dataset parameters, but create a report parameter
> like
> ParamSubSelect type String and then set its default value to something
> like
> "(Select customerNumber from customers where customers.customerName like
> '%Z%')"
>
> Then edit the beforeOpen method of the dataset and set queryText to the
> following:
> queryText ="select * from customers where (Customers.customerNumber IN " +
> params["ParamSubSelect"] + ")";
>
> This should do what you are looking for.
> One caveat is that you will get exception in the designer when you first
> open the report, but the report will run. I am looking into why the error
> occurs.
>
> Jason Weathersby
> BIRT PMC
>
>
>
>
>
>
> "floor" <floorflux@hotmail.com> wrote in message
> news:dg6q4g$sio$1@news.eclipse.org...
>> Can I use a nested query as a parameter in BIRT?
>>
>> I created a Data Set with the following SQL Query:
>>
>> SELECT FirstName
>> FROM Users
>> WHERE (Users.Firstname IN (?))
>>
>> Then I set param1 to be a String with a default value of:
>>
>> "SELECT Users.FirstName FROM Users WHERE Users.FirstName like 'm%'"
>>
>> I know this is a silly query when you add the parameter in place of the
>> ?, but this is just a simplified example of the functionality I need. I
>> would expect this to return all users who's first name starts with the
>> letter 'm', but I get no results when I click Preview Results or run
>> some report with this dataset.
>>
>> Can I use parameters in this way? Am I just misusing my quotation marks
>> or something like that?
>>
>> Thanks for the info!
>>
>> --Floor
>>
>>
>>
>>
>
>

Previous Topic:	How to get the resultset from dataset?
Next Topic:	TextDataHandle ????

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Jun 03 08:48:19 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter