Test for Existence by Retieving a Single Row

When you need to check if at least one row matches a particular criteria, for best performance select only the primary key field and just the first row of the result. If it's not null, then the existence test succeeds.

Object Function: Boolean employeeExistsInDepartmentWithJob( Long department, String jobCode)

def employees = newView('StaffMember')
addBindVariable(employees,'Job','Text')
addBindVariable(employees,'Dept','Number')
// Make sure that either JobId or DepartmentId is indexed!
employees.appendViewCriteria('JobId = :Job and DepartmentId = :Dept')
// Retrieve only the primary key field
selectAttributesBeforeQuery(employees,['EmployeeId'])
setBindVariable(employees,'Job',jobCode)
setBindVariable(employees,'Dept',department)
employees.executeQuery()
// Retrieve just the first row!
return employees.first() != null

Using the queryMap() or queryRow() helper functions described in Simplifying Business Logic Queries you can perform the same optimized query in the example above with fewer lines of code like this:

Object Function: Boolean employeeExistsInDepartmentWithJob( Long department, String jobCode)

// Retrieve only the primary key field and just the first row
return adf.util.queryRow(select:'EmployeeId',
                           from: 'StaffMember',
                          where: 'JobId = :Job and DepartmentId = :Dept',
                          binds:[Job:jobCode,Dept:department]) != null

With the employeeExistsInDepartmentWithJob() helper function in place, our business logic in the StaffMember object can use it like this:

if (employeeExistsInDepartmentWithJob(50,'SH_CLERK')) { /* etc. */ }