The Problems with Table.select(..)

Do not use Openlink’s Table.select(..) method. See the code snippet below for an example of what to avoid.

There is always a better way to implement the required business logic without resorting to this method. Here is a list of some of the problems with the API:

1. When there is a problem running a Table.select(..) statement at runtime, the statement will fail silently. Sometimes there will be an exception detail logged to the error console in the background but unless you are watching the console, you will not know. Even when you are watching the console, you will likely have no knowledge of which statement caused the error message to be printed to the console (see problem #3). There will not be an exception thrown, so the developer cannot catch the exception and throw it up the stack.
2. Even when a Table.select(..) statement works reliably, it often has an unfortunate side-effect in the code of obfuscating the underlying business processing logic. In the following snippet, it is difficult to translate the code back into the business logic that was described by the client analyst. In this implementation, there are comments, which is helpful, for as long as the comments remain accurate. But, all too often…
3. Developers that use one Table.select(..) statement find themselves using a chain of several Table.select(..) statements consecutively.
4. It is more difficult to create automated test cases when using Table.select(..) statements. Unit tests are most easily developed on system-agnostic POJOs. Using Table.select(..) requires a connection to an active Openlink database and an instantiated com.olf.openjvs.Table object. Including a Table object in a POJO dirties the object and makes it difficult to create a reliable system-agnostic and version-agnostic mock to perform automated unit tests.
5. Table.select(..) statements can introduce project risk during an upgrade. Its behavior may change subtly from one version to another.
6. The one periodic, and arguably rare, upside to using a Table.select(..) is that sometimes all that a plugin requires is a short statement including one Table.select(..) for a fully-resolved, performant solution. This is a rare occurrence so be skeptical. As soon as that initial simple solution begins to grow, retire the Table.select(..) technical debt to the ash heap to which it belongs.

//Remove cashflows settling today
mtmT.select(tCflowByDay, "SUM, cflow","deal_num EQ $deal_num AND deal_leg EQ $deal_leg and cflow_date EQ " + scenDate + " and ext_val EQ 0");

//Remove pending buyout proceeds (current buyout proceeds handled above)
mtmT.select(tCflowByDay, "SUM, discounted_cflow (buyout)", "deal_num EQ $deal_num AND deal_leg EQ $deal_leg " +
        " and cflow_type EQ " + CFLOW_TYPE.BUYOUT_CFLOW.toInt() + " and cflow_date GT " + scenDate + " and ext_val EQ 0");

//Remove option payout events
mtmT.select(tCflowByDay, "SUM, discounted_cflow (option)", "deal_num EQ $deal_num AND deal_leg EQ $deal_leg " +
        " and cflow_type EQ " + CFLOW_TYPE.OPTION_PAYOUT_CFLOW.toInt() + " and cflow_date GT " + scenDate + " and ext_val EQ 0 and option_known EQ 1");

//Remove upfront cashflows
mtmT.select(tCflowByDay, "SUM, discounted_cflow (upfront)", "deal_num EQ $deal_num AND deal_leg EQ $deal_leg " +
        " and cflow_type EQ " + CFLOW_TYPE.UPFRONT_CFLOW.toInt() + " and cflow_date GT " + scenDate + " and ext_val EQ 0");

//Remove premium cashflows
mtmT.select(tCflowByDay, "SUM, discounted_cflow (premium)", "deal_num EQ $deal_num AND deal_leg EQ $deal_leg " +
        " and cflow_type EQ " + CFLOW_TYPE.PREMIUM_CFLOW.toInt() + " and cflow_date GT " + scenDate + " and ext_val EQ 0");

//Remove CDS Purchased Interest cashflows
mtmT.select(tCflowByDay, "SUM, discounted_cflow (purchased_interest)", "deal_num EQ $deal_num AND deal_leg EQ $deal_leg " +
        " and cflow_type EQ " + Ref.getValue(SHM_USR_TABLES_ENUM.CFLOW_TYPE_TABLE,"CDS Purchased Interest") + " and cflow_date GT " + scenDate + " and ext_val EQ 0");

//Remove Default Payout cashflows
mtmT.select(tCflowByDay, "SUM, discounted_cflow (payout)", "deal_num EQ $deal_num AND deal_leg EQ $deal_leg " +
        " and cflow_type EQ " + CFLOW_TYPE.DEFAULT_PAYOUT_CFLOW.toInt() + " and cflow_date GT " + scenDate + " and ext_val EQ 0");