I am writing a report in BIRT. I have two input parameters on my data set.
The second parameter I would like to use twice in my where clause. I found I cam do this using a with clause. Note this is a postgresql database so I do not need from dual.
My sql is as follows:
with "params" as (select ? as "sname", ? as "ename")
select "user"."fullName", "user"."address1", "user"."address2", "user"."city", "provinces"."abbreviation", "user"."postalcode", "client"."companyName"
from "user", "params"
LEFT JOIN "client" on "user"."client" = "client"."id"
LEFT JOIN "provinces" on "user"."province" = "provinces"."id"
WHERE "user"."fullName" >= "params"."sname" and (("user"."fullName" <= "params"."ename") or ("params"."ename" =''))`
When I try to run this or preview the results in BIRT's edit data set screen I get the following error:
Error happened while running the report. at .... Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details: Failed to prepare the query execution for the data set: user Cannot get the result set metadata. org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object. SQL error #1:ERROR: invalid reference to FROM-clause entry for table "user" Hint: There is an entry for table "user", but it cannot be referenced from this part of the query. Position: 252;
I can't get by this. From everything I have seen it should work.
You can add multiple parameters in your SQL-Query in Birt and reuse an Input-Parameter from Birt as often as you want. Compare my answer to this question.
So you can write your query without the with-clause and prevent the mixing of explicit and implicit joins:
select "user"."fullName", "user"."address1", "user"."address2", "user"."city", "provinces"."abbreviation", "user"."postalcode", "client"."companyName"
from "user"
LEFT JOIN "client" on "user"."client" = "client"."id"
LEFT JOIN "provinces" on "user"."province" = "provinces"."id"
WHERE "user"."fullName" >= ? and (("user"."fullName" <= ?) or (? =''))