Views - advanced

Derived fields

Derived fields are used to serve up data that is constructed from one or more fields; this is modified programatically before progressing.

For example, if a table has a Notional field and a Price field, you could define a Payment_Amount derived field as Notional * Price.

There are two ways of defining a derived field:

• Using withInput enables you to specify one more more fields as the basis for calculating the value of the new derived field.
• Using withEntity enables you to specify a table entity as the input.

We shall look at each of these below.

Using withInput

This technique enables you to create a derived field from one or more existing fields within the tables in the view. (Use joins if you need bring other tables into the view.)

To start, declare a derivedField and give it a name and field type. Then you can declare the withInput and specify the fields and the logic.

Here is an example where we define a derived field called SPREAD with field inputs.

• The parameter bid receives its value from the field INSTRUMENT_PRICES.BID_PRICE.
• The parameter ask receives its value from the field INSTRUMENT_PRICES.ASK_PRICE.

derivedField("SPREAD", DOUBLE) {
  withInput(INSTRUMENT_PRICES.BID_PRICE, INSTRUMENT_PRICES.ASK_PRICE) { bid, ask ->
    if (ask == null || bid == null) null
      else ask - bid
    }
}

info

The maximum number of fields that can be used as input of a derived field is 10.

Using withEntity

This technique enables you to add a table entity as an input, which gives you access to all fields in that table.

To start, declare a derivedField and give it a name and field type. Then you can declare the withEntity and specify the table, the fields and the logic.

Here is an example where we define a derived field called SPREAD with the entity as the input. The table (entity) is INSTRUMENT_PRICES and the logic uses two fields from this table: askPrice and bidPrice.

derivedField("SPREAD", DOUBLE) {
    withEntity(INSTRUMENT_PRICES) { price ->
        price.askPrice - price.bidPrice
    }
}

Using withEntity gives two useful advantages:

• If you require multiple fields from the same table as input into a derived field, you can pass the table object as a parameter and use any field from it without having to make multiple withInput statements.
• Non-null fields on the entity will be non-null.

By default, all fields in the entity are returned. So there could be a performance impact if you are using large tables; it is likely that your calculations won't need many of the fields are loaded in your calculation or in the final view.

There two ways of mitigating this.

The first way is to return only non-null fields. Here is an example:

derivedField("SPREAD", DOUBLE) {
    withEntity(INSTRUMENT_PRICES, onlyNonNullFields = true) { price ->
        price.askPrice - price.bidPrice
    }
}

The second way is to specify the fields to be returned. Other fields in the table are not returned. Here is an example:

derivedField("SPREAD", DOUBLE) {
    withEntity(INSTRUMENT_PRICES, fields = listOf(INSTRUMENT_PRICES.ASK_PRICE, INSTRUMENT_PRICES.BID_PRICE)) { price ->
        price.askPrice - price.bidPrice
    }
}

Joins

INNER vs OUTER joins

Available join types are INNER and OUTER. If you do not specify the type, it defaults to OUTER.

• INNER joins require all joins to match exactly; if one single join fails to match, the row will be discarded.
• OUTER joins provide null references for failed joins and will still allow the row to be built.

joining(INSTRUMENT, JoinType.INNER) {
    on(TRADE { INSTRUMENT_ID } to INSTRUMENT { INSTRUMENT_ID })

Dictionary-joined tables

When tables are joined in the dictionary, you are able to join to those tables in views directly, without having to specify the fields on which to join. This does not currently work with aliased tables.

Joining on fields:

joining(TRADE_TO_SIDE) {
    on(TRADE { TRADE_ID } to TRADE_TO_SIDE { TRADE_ID })

Joining using join:

joining(TRADE.JOIN_TRADE_TO_SIDE)

Parameterised joins

Some join operations require external parameters that are not available in the context of the table-join definition, but will be available when the view repository is accessed (e.g. client-enriched definitions), so an option exists to create parameterised joins.

These are typically used in Request Server queries:

view("INSTRUMENT_PARAMETERS", INSTRUMENT) {
    joins {
        joining(ALT_INSTRUMENT_ID, JoinType.INNER) {
            on(INSTRUMENT.ID to ALT_INSTRUMENT_ID.INSTRUMENT_ID)
                .and(ALT_INSTRUMENT_ID.ALTERNATE_TYPE.asParameter())
        }
    }
    fields {
        ALT_INSTRUMENT_ID {
            ALTERNATE_CODE withAlias "INSTRUMENT_CODE"
        }
        INSTRUMENT {
            NAME withPrefix INSTRUMENT
        }
    }
}

So for the above, if we had a Request Server using the view, it would make ALTERNATE_TYPE available as a field input parameter.

Dynamic joins

These have a shared syntax with derived fields. However, rather than specifying a field name and type, it should always return an entity index type of the table you’re joining on.

warning

When using dynamic joins on aliased tables, the alias name should match the alias variable name. E.g.: val fixCal = TRADE_CALENDAR withAlias "fixCal", here it is fixCal in both cases.

Object Type	Name
Table	TRADE
Entity	Trade
Index	TRADE_BY_ID
Entity Index	Trade.ById

As with derived fields, you can use the withEntity and the withInput syntax. However, the lambda should always return an entity index object or null. Also, it should always return the same type. It is not possible to switch dynamically between indices, so it should always return the same type or null. It is possible to add further and clauses afterwards.

Syntax:

joining({usual join syntax}) {
   on {  
      // either
      withEntity({table name}) {
        // build index entity here
      }
      // or
      withInput({field 1}, {field 2}, .., {field 9}) { a, b, .. ->
        // build index entity here
      }
   }
}

Multiple joins on the same table

To be able to perform multiple joins on the same table, you need to use one or more aliases:

    val firm = BID_OFFER withAlias "firm"
    val draft = BID_OFFER withAlias "draft"

    joins {
        joining(firm, backwardsJoin = true) {
            on(firm { BID_STATE } to BidState.FIRM)
        }
        joining(draft, backwardsJoin = true) {
            on(draft { BID_STATE } to BidState.DRAFT)
        }
    }
    fields {
        firm {
            BID_PRC withAlias "FIRM_BID_PRC
        }
        draft {
            BID_PRC withAlias "DRAFT_BID_PRC"
        }
    }

Examples

Example 1

Before:

joining(fix, backwardsJoin = true) {
   on(TRADE_TO_SIDE { FIX_ID } to fix { SIDE_ID })
      .and(fix { SIDE_TYPE } to SideType.FIX)
      .joining(fixCal, JoinType.INNER, backwardsJoin = true) {
        on(fix { CALENDAR_ID } to fixCal { CALENDAR_ID })
      }
}

After:

joining(fix, backwardsJoin = true) {
   on {
      withEntity(TRADE_TO_SIDE) { tradeToSide ->
        TradeSide.BySideId(tradeToSide.fixId)
      }
   }
   .and(fix { SIDE_TYPE } to SideType.FIX)
   .joining(fixCal, JoinType.INNER, backwardsJoin = true)
}

Example 2

Before:

joining(fixCal, JoinType.INNER, backwardsJoin = true) {
    on(fix { CALENDAR_ID } to fixCal { CALENDAR_ID })
}

After:

.joining(fixCal, JoinType.INNER, backwardsJoin = true) {
   on {
      withInput(fix { CALENDAR_ID }) { calendarId ->
         when (calendarId) {
            null -> null
            else -> TradeCalendar.ByCalendarId(calendarId)
         }
      }
   }
}