Views - basics

A view definition is made up of joins and fields.

Joins

Starting with one table (the root table), a join enables you to fetch one or more fields from another table so that it can be served and viewed as a single query row. For any join, one of the fields must be an index.

For example, you might want to join a TRADE to an INSTRUMENT in order to pull in INSTRUMENT fields such as the NAME or CURRENCY to serve up to a grid.

views {

  view("TRADE_VIEW", TRADE) {

    joins {
      joining(INSTRUMENT) {
        on(TRADE.INSTRUMENT_ID to INSTRUMENT { INSTRUMENT_ID })
      }
    }

    fields {
      TRADE.allFields()

      INSTRUMENT.NAME withPrefix INSTRUMENT
      INSTRUMENT.CURRENCY_ID withAlias "CURRENCY"
    }
  }
}

You can define multiple joins onto any given table.

You can also add nested joins (join to a joined table), and so on...

The following example shows three key things:

• It joins the table POSITION onto ALT_INSTRUMENT_ID on a 2-field key. One of the join values (ALTERNATE_TYPE) uses a literal value as opposed to stemming from a field in POSITION.

• There is also a nested join, from ALT_INSTRUMENT_ID onto INSTRUMENT_L1_PRICE.

• Finally, there is a join from POSITION onto the INSTRUMENT table.

    joins {
      joining(ALT_INSTRUMENT_ID) {
        on(POSITION.INSTRUMENT_ID to ALT_INSTRUMENT_ID { INSTRUMENT_ID })
          .and(ALT_INSTRUMENT_ID { ALTERNATE_TYPE } to "REFINITIV")                            

          .joining(INSTRUMENT_L1_PRICE) {
            on(ALT_INSTRUMENT_ID.INSTRUMENT_CODE to INSTRUMENT_L1_PRICE { INSTRUMENT_CODE }) 
          }
      }

      joining(INSTRUMENT) {
        on(POSITION.INSTRUMENT_ID to INSTRUMENT { INSTRUMENT_ID })
      }
    }

It is worth noting that only the root table is monitored for changes by default. if you are working with real-time data in your views, you might need to use backwards joins, which we shall look at later in this page.

Joins can be one-to-one (key field match) or one-to-many (part-key-field match).

warning

Views with one-to-many joins cannot be used with Data Servers.

Fields

Fields allow you to define which fields you would like to include in your view. You can reference fields from any of the tables that have been joined inside your view.

Adding a field is as simple as typing it in the fields section of the view.

    fields {
      INSTRUMENT.CURRENCY_ID 
    }

You can add all the fields from a given table to a view using the allFields accessor.

    fields {
      TRADE.allFields()
    }

note

You can also access derived fields that were defined in the tables dictionary. This is just the same as any other field. For example, if the table TRADE has a derived field called FAVOURITE, you could use it as follows:

    fields {
      TRADE.FAVOURITE
    }

Overriding a field name

You can override the name of a field using various operators:

• withAlias - gives the field an alternative name on the view
• withPrefix - adds a prefix to the standard field name; this is useful if you have a clash (e.g. if COUNTERPARTY and INSTRUMENT both have a NAME field)

      COUNTERPARTY.NAME withPrefix COUNTERPARTY
      INSTRUMENT.NAME withPrefix INSTRUMENT
      INSTRUMENT.CURRENCY_ID withAlias "CURRENCY"

Backwards joins

Backwards joins are important if your view is used in any real-time activity - for example, if you are going to stream data from the view or if you have consolidators monitoring the view.

• By default, the platform (specifically, the Update Queue) only listens for changes on the root table of the view.

• If you want to include real-time updates from the fields in a joined table, then you have to specify that this a backwards join. With a backwards join, the joined table is monitored for changes as well as the root table.

Below is an example view that contains a default join and backwards joins. The root table is the POSITION table, and it has two joins:

• The first join has two backwards joins to ensure real-time updates of all fields; the first is to the ALT_INSTRUMENT_ID table, where it picks up an alternative ID for the instrument; the second is to the INSTRUMENT_L1_PRICE table, to pick up the price for the instrument. This is effectively a chain, so note that you cannot make a normal join and then make a backwards join from that table to another one. That would not work.
• The second join is from the root table (POSITION) to the INSTRMENT table, where it picks up the instrument ID. There is no requirement to monitor this in real time, so this is not a backwards join.

  view("POSITION_VIEW", POSITION) {

    joins {
      joining(ALT_INSTRUMENT_ID, backwardsJoin = true) {
        on(POSITION.INSTRUMENT_ID to ALT_INSTRUMENT_ID { INSTRUMENT_ID })
          .and(ALT_INSTRUMENT_ID { ALTERNATE_TYPE } to "REFINITIV")

          .joining(INSTRUMENT_L1_PRICE, backwardsJoin = true) {
            on(ALT_INSTRUMENT_ID.INSTRUMENT_CODE to INSTRUMENT_L1_PRICE { INSTRUMENT_CODE })
          }
      }

      joining(INSTRUMENT) {
        on(POSITION.INSTRUMENT_ID to INSTRUMENT { INSTRUMENT_ID })
      }
    } 
  }

Do not use backwards joins unnecessarily.

There is a processing overhead in monitoring the joined tables. In some cases, this can be significant.

Only specify a backwards join where it is essential to monitor the fields in real time.