If you are familiar with relational databases then you have almost certainly encountered views, and have probably used them in certain situations where it is preferable to creating new tables within your schema. In simple terms, a view (in SQL) is stored as a query on a particular dataset; this query is executed whenever the view is invoked, and therefore reflects changes in the table to which it refers.
A Custom View provides a way to refer to a database table with an SQL statement applied, typically to filter the rows and columns. In a Custom View, the definition of the view is held within the platform rather than the database, and can easily be modified and copied. However, a Custom View cannot be queried elsewhere in the Workspace platform (e.g. within the R console) and so must be converted into a Database View if this is required.
To create a Custom View, open the relevant table and click the ‘Derive a custom view’ button, then:
- Optionally set up one or more filters, choosing field, operator, and value.
- Optionally choose one or more fields by clicking the list. The chosen fields appear in the panel in the right hand side of the window and can be removed from there by clicking on the ‘X’ button.
- Click the ‘Verify custom view’ button.
In the popup, review the SQL statement. If required, you can manually edit the SQL statement from here.
Once you are ready to do so, click the ‘Create custom view’ button and provide a name e.g ‘anon_bed_days_2’. A newly created custom view of the dataset will appear in the datasets list in the Datasets tab.
To create a Database View from a Custom View, highlight the Custom View on the Datasets tab then click the ‘Create as a database view’ button.