Parameters in SQL Statements

Hi, 
 
hope the question isn't asked before. I used the search but couldn#t find something helpful. 
 
I have defined two parameters in Report Parameters: RP_startDate and RP_endDate. Both are of Type Date. 
 
In my DataSet i also defined two parameters ("Edit Data Set" -> "Parameters"): startDate and endDate, and referenced them to the report Parameters. 
 
My SQL Query is: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select DISTINCT public.fragebogen.id, public.fragebogen.name, public.fragebogen.vorname, public.fragebogen.titel
from public.fragebogen
WHERE ausgefuellt_am between param["startDate"].value and param["endDate"].value
</pre>
 
When i switch to preview i can do the Input for the 2 Dates but I am getting the following Error: 
<blockquote class='ipsBlockquote' >
Table (id = 249): 
+ An exception occurred during processing. Please see the following message for details: 
Failed to prepare the query execution for the data set: gesamt_BoegenID_perMonth 
Cannot set the date value (0011-11-01 00:00) to parameter 1. 
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter date value. 
SQL error #1:Der Spaltenindex 1 ist au?erhalb des g?ltigen Bereichs. Anzahl Spalten: 0. 
; 
org.postgresql.util.PSQLException: Der Spaltenindex 1 ist au?erhalb des g?ltigen Bereichs. Anzahl Spalten: 0. (Element ID:249) </blockquote>
 
Can somebody help me? 
 
Cheers...

Find more posts tagged with

Comments

Yogesh Maharwade

hi

if you are doing scripted data sourse report,

initialize a variable in your data set open script

startDate = MXReportSqlFormat.getStartDayTimestampFunction(params["startDate"]);
endDate= MXReportSqlFormat.getStartDayTimestampFunction(params["endDate"]);

and pass this variable in your sql query like

select DISTINCT public.fragebogen.id, public.fragebogen.name, public.fragebogen.vorname, public.fragebogen.titel
from public.fragebogen
WHERE ausgefuellt_am between "+ startDate +" and "+ endDate+"

make sure that your parameter name and variable name are same.

piwya

CBR

Hi,

if you want to use parameters in your SQL statement replace the parameter by a ? (question mark).
After that there should be a entry in the parameters tab of the dataset. Configure each entry to be bound to the appropriate report parameter.

You added the parameters directly to the SQL which doesn't make much sense. The SQL itself is a string and script variables or expressions are not replaced with their value at runtime.

Yogesh Maharwade

yup...this is the right answer..i was thinking that MALPI is doing scripted data set report..

cmbr25

<blockquote class='ipsBlockquote' data-author="'cbrell'" data-cid="85536" data-time="1321884439" data-date="21 November 2011 - 07:07 AM">
Hi, 
 
if you want to use parameters in your SQL statement replace the parameter by a ? (question mark). 
After that there should be a entry in the parameters tab of the dataset. Configure each entry to be bound to the appropriate report parameter. 
 
You added the parameters directly to the SQL which doesn't make much sense. The SQL itself is a string and script variables or expressions are not replaced with their value at runtime. </blockquote>

You could help me in passing a parameter in the filter dataset? I need to pass a value dynamically, as well, I explain every time I change the page is changed and the course code (via the page brake (always before). I need to get this code and bad for a parameter and use the filter the dataset. how would I do? I tried to retrieve the value causing the parameter I created a "c?digo_disciplina" receive the value of the code of discipline of the dataset of the crosstab query. But as the query has yet been mounted I can not ask for the filter to be respected. Help me thank you very much for your cooperation.

CBR

@cmbr
Sorry i already thought about answering you in your thread but to be honest i can hardly understand what you are trying to do.

MALPI

cmbr25

<blockquote class='ipsBlockquote' data-author="'cbrell'" data-cid="85540" data-time="1321887564" data-date="21 November 2011 - 07:59 AM">
@cmbr 
Sorry i already thought about answering you in your thread but to be honest i can hardly understand what you are trying to do. </blockquote>

Excuse me is that I am Brazilian and I'm using google translator to translate English to my questions and can be confusing to understand. Could you help me to put the new topic, I believe is best to understand ... 
It is the last post topic! Thank you in advance for your interest to help me.

CBR

<blockquote class='ipsBlockquote' data-author="'MALPI'" data-cid="85544" data-time="1321889544" data-date="21 November 2011 - 08:32 AM">
Hi all, 
 
Tahnks for answering. This answer was what I was looking for. But how does BIRT know which param matches which "?" ? 
 
Greets, 
 
MALPI </blockquote>
 
Hi, 
 
BIRT knows which ? matches which parameter because both (the parameter in the parameter tab and the ? in the query) have a order. It simply substitutes the first ? by the query parameter that is on first position of parameter tab list, second with second and so on.

MALPI

<blockquote class='ipsBlockquote' data-author="'cbrell'" data-cid="85563" data-time="1321903068" data-date="21 November 2011 - 12:17 PM">
Hi, 
 
BIRT knows which ? matches which parameter because both (the parameter in the parameter tab and the ? in the query) have a order. It simply substitutes the first ? by the query parameter that is on first position of parameter tab list, second with second and so on. </blockquote>
 
Hi, 
 
ok now it is clear for me. Thanks

Greets, 
 
MALPI

Cheong Hoong Jun

hi,
 
do u all know how to pass in parameter by parameter name instead of by sequence in '?'.
 
thanks

Cheesus Crust

I've yet to encounter a situation where a parameter required more precise naming than which the simple ordered sequence couldn't handle, because you can always assign each BIRT report parameter "?" to a SQL variable if you need to use them in a different order in your query. 
 
For example:
 
DECLARE
 @var1 FLOAT = ?
,@var2 VARCHAR(20) = ?
 
SELECT
bar.foo
FROM dbo.bar 
WHERE
dbo.bar.baz LIKE @var2 
AND dbo.bar.Price > @var1