You can create a derived table from a method that returns a collection of objects. The parameters (if any) of such a method must be JADE primitive types whose usage is input or constant.
If the collection method returns a transient collection, that collection is deleted when the query has completed.
A table derived from a method returning a collection has two columns of object identifiers (oids): one maps to the instance of the object on which the methods is defined and the other maps to instances of the collection returned by the method.
In addition, a defined column corresponds to each of the parameters in the methods. For example, a Company class method getCustomerBalances(minBal: Decimal; maxBal: Decimal); that returns a list of customers whose outstanding balance is in the range minBal less than or equal to bal less than or equal to maxBal creates a derived table called Company_getCustomerBalances that can have the following four columns.
company_oid
customer_oid
minBal
maxBal
The derived table represents a virtual collection of customers whose outstanding balance is in the range minBal through maxBal. The values of minBal and max Bal are included in the WHERE clause of the select statement and are used as the parameter values passed to the getCustomerBalances method.
The logical relationship between Company, Company_getCustomerBalances, and Customer is shown in the following Entity-Relationship Diagram (ERD).
The following SQL statement selects customers with a balance in the range $500 through $5000.
SELECT Customer.name, Customer.balance
FROM Company, Customer, Customer_getCustomerBalances
WHERE Company.oid=Company_getCustomerBalances.company_oid AND
Customer.oid=Company_getCustomerBalances.customer_oid AND
Company_getCustomerBalances.minBal = 500 AND
Customer_getCustomerBalances.maxBal = 5000
When specifying table columns in the WHERE clause that correspond to the parameters of a collection method, any comparison must be for equality (that is, the equals symbol only) and used in an AND operation. (An error is raised if this is not the case.)
If a parameter is not specified, the method is passed a NULL value for that parameter.
Exceptions raised by methods that are mapped to columns are logged if the
For methods that are mapped to ODBC columns, it is better to handle exceptions within your user code and return a valid value rather than propagating the error out to the ODBC driver code.
Although the following SQL statement would achieve the same result as the previous example, using the getCustomerBalances method may significantly reduce the io required to complete the query. (This would be particularly relevant if getCustomerBalances were returning an existing collection and not building one on each invocation of the method.)
SELECT Customer.name, Customer.balance
FROM Company, Customer
WHERE Customer.company=Company.oid AND
Customer.balance >= 500 AND
Customer.balance >= 5000
