How to use one param in multiple places in birt report

Question

I want to pass one param in birt report and use it in multiple palces , for example :

SELECT * FROM tab1 WHERE  startDAte = trunc(sysdate)
UNION
SELECT *FROM tab1  WHERE startDate= trunc(sysdate-1)

So i want to make sysdate like a variable :

SELECT * FROM tab1 WHERE  startDAte = trunc(?)
UNION
SELECT *FROM tab1 WHERE startDate= trunc(?-1)

How can i do that ? Thanks

Answer 1

This has already been answered in Reusing an anonymous parameter in a prepared statement

If your database is Oracle, you can also use the following syntax:

WITH params AS
( select ? as p_date,
         ? as p_whatever,
         ...
  from dual
)
SELECT tab1.* FROM tab1, params WHERE tab1.startDate = trunc (params.p_date)
UNION
SELECT tab1.* FROM tab1, params WHERE startDate= trunc (parms.p_date - 1)

Note that this does not have a negative performance impact, because the DB is clever enough to detect that the params inline-view contains exactly one row.