SQL statements currently built by Jasper in the Ad Hoc tool will never contain "exists" or "in" clauses.
Example: A customer would like a list of donors who have any gift with fund ABC. Donors are stored in the DONOR table and gifts are stored in the GIFT table; a donor can have multiple gifts. If a donor has a gift with fund ABC, they should show up on the report one time. If a donor has multiple gifts with fund ABC, they should still only show up on the report one time. The SQL I would expect to get built would look something like this:
select ...
from donor
where idnumber in (select idnumber from gift where fundcode = 'ABC');
Jasper will never currently build that kind of SQL. Instead, it will build something like this:
select ...
from donor d, gift g
where d.idnumber = g.idnumber
and g.fundcode = 'ABC';
To be able to have an option to use an "exists" or "in" clause would make Ad Hoc much more useable and fix many of our users' complaints.
Components | JasperReports Server, AdHoc, Filters, Performance |