0

I have this where clause:

WHERE p.ROLE = 'doctor' AND((p6.PA_Name='Event_Day_From' AND p6.PA_Value>= SUBSTRING('01.01.2012', 1, 2)) AND (p7.PA_Name='Event_Month_From' AND p7.PA_Value>=SUBSTRING('01.01.2012', 4, 2)) AND (p8.PA_Name='Event_Year_From' AND P8.PA_Value>=SUBSTRING('01.01.2012', 7, 4))) AND ((p9.PA_Name='Event_Day_To' AND P9.PA_Value<=SUBSTRING('30.12.2012', 1, 2)) AND (p10.PA_Name='Event_Month_To' AND P10.PA_Value<=SUBSTRING('30.12.2012', 4, 2)) AND (p11.PA_Name='Event_Year_To' AND P11.PA_Value<=SUBSTRING('30.12.2012', 7, 4)))

after the above I have an union all and at the end I have another WHERE clause exactly the same as the above.

In BIRT a parameter can be passed and you have to insert ? in the SQL query where you need it to be passed. As you can see I have a start date and an end date which will be passed through an user input. Now my problem is that I have no idea how to pass the SDate parameter to all the start dates and the EndDate parameter to all the end dates

Is there a solution for my problem?

Improve this question

2 Answers 2

Reset to default
1

I solved this problem by creating table with a single row from the parameters, and attaching it to the report query like so:

,(select ? SDate, ? EDate) params WHERE p.ROLE = 'doctor' AND((p6.PA_Name='Event_Day_From' AND p6.PA_Value>= SUBSTRING(params.SDate, 1, 2)) AND (p7.PA_Name='Event_Month_From' AND p7.PA_Value>=SUBSTRING(params.SDate, 4, 2)) AND (p8.PA_Name='Event_Year_From' AND P8.PA_Value>=SUBSTRING(params.SDate, 7, 4))) AND ((p9.PA_Name='Event_Day_To' AND P9.PA_Value<=SUBSTRING(params.EDate, 1, 2)) AND (p10.PA_Name='Event_Month_To' AND P10.PA_Value<=SUBSTRING(params.EDate, 4, 2)) AND (p11.PA_Name='Event_Year_To' AND P11.PA_Value<=SUBSTRING(params.EDate, 7, 4)))

Hope this helps.

**I use this method on a PostgreSQL database currently.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

what are the names of your parameters here ? and how does BIRT get them ?
1

[EDIT] BIRT uses plain JDBC not it's not supporting "named parameters", so you can't write:

... p6.PA_Value>= SUBSTRING(:start, 1, 2) ...

This will give an error.

Other options are to build the query using Property Binding or JavaScript and replace parts of it before it is executed. See this example report

Some databases also support the WITH syntax to assign an alias to a ? parameter. This blog post explains how to use this.

Improve this answer

5 Comments

Which database? There is a workaround when you use Oracle: enterprisesmartapps.wordpress.com/2011/01/10/…
I am using microsoft sql server
Okay, I've completely overworked my answer. Try the dynamic query modification. It's not perfect but works in your case since you only need strings.
is there a way to pass the parameters to a procedure?
BIRT supports calling stored procedures: wiki.eclipse.org/StoredProcedure_(BIRT)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.