The EXISTS keyword in SQL queries is an efficient mechanism for returning record sets from one dataset when they exist in another dataset. For example we can write queries like:

SELECT org.org_id, org.name FROM organisations org WHERE EXISTS
(SELECT 1 FROM events evt WHERE evt.org_id = org.org_id
AND evt.contact_name = 'Eddie Harris')

....which returns all organisations which have a related event whose contact is Eddie Harris.

ADF Business Components in JDeveloper 11g allow the creation of EXISTS subqueries via the View Object named View Criteria feature. They're easy to implement if you already know how to create View Criteria, as long as you know one small trick.

Default Business Components
Given the SQL query above using the one-to-many organisations-to-events example, imagine we have default Entity Objects (EOs), EO Associations, View Objects (VOs) and VO Links, as seen in this picture:

View Object Link Accessors

When created via the Business Components from Table wizard, the VO Link OrgEvtFkLink created, based on the EO Association OrgEvtFkAssoc, will include Accessors options under its Relationship tab in the VO Link editor:

If you select the pencil icon next to the Accessors options it reveals the View Link Properties dialog:

....from which you can see the "Generate Accessor" option selected for the "In View Object: Organisations View". While the selected state is the default option when created, it's this option which is essential for setting up the EXISTS View Criteria.

View Object View Criteria
Once you've ensured the Accessor option is set as described above, when you create a new View Criteria for the View Object, Organisations in our case, we configure the View Criteria as follows. First in the Create View Criteria dialog we should change the View Criteria name to something more suitable to reflect what the View Criteria will do for us:

Next select the Add Criteria button, which will create the basis of the expression used by the query:

On selecting the Attribute drop down, you'll discover a list of attributes from the OrganisationsView VO. In this list you'll note an attribute called "EventsView". This attribute is only available because of the options you configured in the View Object Link Accessors above. If you hadn't gone with the default options, the EventsView attribute would not be available, and you would not be able to create the EXISTS View Criteria:

With the EventsView Accessor selected the dialog for the first time shows the EXISTS clause:

The only think left to do is to select the Criteria Group expression of the EXISTS statement, in the example above this is the "Event No =" option, and change this using the supplied options in the fields below, to the actual expression we want to use in the EXISTS clause. From our example this is matching the Events Contact Name to a String:

Once completed in the right hand side you can see the EXISTS subquery that the View Criteria will apply to the OrganisationsView VO when executed.

Note I've also turned off the Ignore Case and Ignore Null Values options.

Testing
In the Business Components Browser, on opening the OrganisationsView, and selecting the View Criteria via the Find button, we're first prompted for a value for the bind variable:

...which once supplied, returns the only matching Organisations record:

Thanks

Thanks to Eddie Harris at SAGE Computing Services for revealing the technique.

Read the original blog entry...