Jasper Ideas

Build SQL statements in Ad Hoc with an "exists" or "in" clause

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.

  • Guest
  • Mar 27 2025
  • To be Reviewed
Components JasperReports Server, AdHoc, Filters, Performance
  • Attach files