CODEWITHSUNDEEP
eclipsebirt
Slim
Slim

Reputation: 1744

BIRT reporting - use the same parameter multiply times in a SQL query

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?

Upvotes: 0

Views: 2682

Answers (2)

user1969350
user1969350

Reputation: 26

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.

Upvotes: 1

Aaron Digulla
Aaron Digulla

Reputation: 328614

[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.

Upvotes: 1

Related Questions