Referencing parameter by name [message #495840] |
Fri, 06 November 2009 11:46 |
Missing name Missing name Messages: 94 Registered: November 2009 |
Member |
|
|
Hi,
Im using the BIRT report designed with a query of the form
select * from myTable where col1 = ? and col2 = ?
where I have defined two input parameters
param1 and param2 which are integers.
This works fine but if I want to do something along the lines of
select * from myTable where col1 = ? and col2 = ? and col3 < ?
it complains about binding, as I assume its confused about which parameter I am comparing against col3. Is it possible to use a parameter more than once in a query, like I want to do?
Can I reference the parameters by name rather than ? such as
select * from myTable where col1 = param1 and col2 = param2 and col3 < param2
I tried various combinations of params["param2"].value and :param2 but cant get it to work
thanks
Tom
[Updated on: Fri, 06 November 2009 11:47] Report message to a moderator
|
|
|
Re: Referencing parameter by name [message #495949 is a reply to message #495840] |
Fri, 06 November 2009 18:18 |
|
Tom,
Either just create a 3rd parameter and set it to the same thing as the
second or drop the dataset parameters and do the query in the before
open script
this.queryText = "select * from myTable where col1 = " +
jsvar/reportparm + ....
Jason
TREVOR.HIGGINS@FINEOS.COM wrote:
> Hi,
> Im using the BIRT report designed with a query of the form
>
> select * from myTable where col1 = ? and col2 = ?
>
> where I have defined two input parameters
>
> param1 and param2 which are integers.
>
> This works fine but if I want to do something along the lines of
> select * from myTable where col1 = ? and col2 = ? and col3 < ?
>
> it complains about binding, as I assume its confused about which
> parameter I am comparing against col3. Is it possible to use a parameter
> more than once in a query, like I want to do?
>
> Can I reference the parameters by name rather than ? such as
>
> select * from myTable where col1 = param1 and col2 = param2 and col3 <
> param2
>
> I tried various combinations of params["param2"].value and :param2 but
> cant get it to work
>
> thanks
>
> Tom
|
|
|
|
|
Re: Referencing parameter by name [message #496336 is a reply to message #496079] |
Mon, 09 November 2009 18:04 |
|
Colin
What code do you want?
Jason
Colin wrote:
> Hi jason,
>
> This sounds like it could be very useful for me. Could you be a bit more
> explicit?
> What would the code be to pick up param1, param2 and param3?
>
> Regards,
> Colin
>
> Jason Weathersby wrote:
>> Tom,
>>
>> Either just create a 3rd parameter and set it to the same thing as the
>> second or drop the dataset parameters and do the query in the before
>> open script
>>
>> this.queryText = "select * from myTable where col1 = " +
>> jsvar/reportparm + ....
>>
>>
>> Jason
>>
>> TREVOR.HIGGINS@FINEOS.COM wrote:
>>> Hi,
>>> Im using the BIRT report designed with a query of the form
>>>
>>> select * from myTable where col1 = ? and col2 = ?
>>>
>>> where I have defined two input parameters
>>>
>>> param1 and param2 which are integers.
>>>
>>> This works fine but if I want to do something along the lines of
>>> select * from myTable where col1 = ? and col2 = ? and col3 < ?
>>>
>>> it complains about binding, as I assume its confused about which
>>> parameter I am comparing against col3. Is it possible to use a
>>> parameter more than once in a query, like I want to do?
>>>
>>> Can I reference the parameters by name rather than ? such as
>>>
>>> select * from myTable where col1 = param1 and col2 = param2 and col3
>>> < param2
>>>
>>> I tried various combinations of params["param2"].value and :param2
>>> but cant get it to work
>>>
>>> thanks
>>>
>>> Tom
|
|
|
Re: Referencing parameter by name [message #496391 is a reply to message #496336] |
Mon, 09 November 2009 20:09 |
Colin Sutton Messages: 121 Registered: July 2009 |
Senior Member |
|
|
I have a report with two parameters which I match dozens of times in a query on multiple database
sources.
It's tedious matching all the ? and setting dataset param1, param2, ... all to the same report
parameter.
In the example below would the complete before open script be:
this.queryText = "select * from myTable where col1 = " + jsvar/reportparam1 + " and col2 = "
jsvar/reportparam2 " and col3 < " jsvar/reportparam1
Regards,
Colin
Jason Weathersby wrote:
> Colin
>
> What code do you want?
>
> Jason
>
> Colin wrote:
>> Hi jason,
>>
>> This sounds like it could be very useful for me. Could you be a bit
>> more explicit?
>> What would the code be to pick up param1, param2 and param3?
>>
>> Regards,
>> Colin
>>
>> Jason Weathersby wrote:
>>> Tom,
>>>
>>> Either just create a 3rd parameter and set it to the same thing as
>>> the second or drop the dataset parameters and do the query in the
>>> before open script
>>>
>>> this.queryText = "select * from myTable where col1 = " +
>>> jsvar/reportparm + ....
>>>
>>>
>>> Jason
>>>
>>> TREVOR.HIGGINS@FINEOS.COM wrote:
>>>> Hi,
>>>> Im using the BIRT report designed with a query of the form
>>>>
>>>> select * from myTable where col1 = ? and col2 = ?
>>>>
>>>> where I have defined two input parameters
>>>>
>>>> param1 and param2 which are integers.
>>>>
>>>> This works fine but if I want to do something along the lines of
>>>> select * from myTable where col1 = ? and col2 = ? and col3 < ?
>>>>
>>>> it complains about binding, as I assume its confused about which
>>>> parameter I am comparing against col3. Is it possible to use a
>>>> parameter more than once in a query, like I want to do?
>>>>
>>>> Can I reference the parameters by name rather than ? such as
>>>>
>>>> select * from myTable where col1 = param1 and col2 = param2 and col3
>>>> < param2
>>>>
>>>> I tried various combinations of params["param2"].value and :param2
>>>> but cant get it to work
>>>>
>>>> thanks
>>>>
>>>> Tom
|
|
|
|
Re: Referencing parameter by name [message #496602 is a reply to message #496391] |
Tue, 10 November 2009 16:07 |
|
Colin,
That is correct. Reference report parameters like
params["myreportname"].value
Jason
Colin wrote:
> I have a report with two parameters which I match dozens of times in a
> query on multiple database sources.
> It's tedious matching all the ? and setting dataset param1, param2, ...
> all to the same report parameter.
> In the example below would the complete before open script be:
> this.queryText = "select * from myTable where col1 = " +
> jsvar/reportparam1 + " and col2 = " jsvar/reportparam2 " and col3 < "
> jsvar/reportparam1
>
> Regards,
> Colin
>
> Jason Weathersby wrote:
>> Colin
>>
>> What code do you want?
>>
>> Jason
>>
>> Colin wrote:
>>> Hi jason,
>>>
>>> This sounds like it could be very useful for me. Could you be a bit
>>> more explicit?
>>> What would the code be to pick up param1, param2 and param3?
>>>
>>> Regards,
>>> Colin
>>>
>>> Jason Weathersby wrote:
>>>> Tom,
>>>>
>>>> Either just create a 3rd parameter and set it to the same thing as
>>>> the second or drop the dataset parameters and do the query in the
>>>> before open script
>>>>
>>>> this.queryText = "select * from myTable where col1 = " +
>>>> jsvar/reportparm + ....
>>>>
>>>>
>>>> Jason
>>>>
>>>> TREVOR.HIGGINS@FINEOS.COM wrote:
>>>>> Hi,
>>>>> Im using the BIRT report designed with a query of the form
>>>>>
>>>>> select * from myTable where col1 = ? and col2 = ?
>>>>>
>>>>> where I have defined two input parameters
>>>>>
>>>>> param1 and param2 which are integers.
>>>>>
>>>>> This works fine but if I want to do something along the lines of
>>>>> select * from myTable where col1 = ? and col2 = ? and col3 < ?
>>>>>
>>>>> it complains about binding, as I assume its confused about which
>>>>> parameter I am comparing against col3. Is it possible to use a
>>>>> parameter more than once in a query, like I want to do?
>>>>>
>>>>> Can I reference the parameters by name rather than ? such as
>>>>>
>>>>> select * from myTable where col1 = param1 and col2 = param2 and
>>>>> col3 < param2
>>>>>
>>>>> I tried various combinations of params["param2"].value and :param2
>>>>> but cant get it to work
>>>>>
>>>>> thanks
>>>>>
>>>>> Tom
|
|
|
Powered by
FUDForum. Page generated in 0.04224 seconds