| Type | Read/write | Author | Availability |
| Data provider | Read | Finbourne | Provided with LUSID |
The Sys.Field provider enables you to write a Luminesce SQL query that describes the shape, input parameters and further details of Luminesce providers.
Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and read system provider data. This should automatically be the case if you are the domain owner.
If a user has access to multiple providers or views of the same name, Sys.Field returns the shape of the provider/view most appropriate to the user, with the following order:
- Locally routed or user-routed providers (this applies to users building their own or running their own copies of providers).
- Providers specific to a slow ring domain (if on a slow ring domain).
- Providers specific to the user's domain, for example if an admin in the domain has added columns to a LUSID provider, or overridden a view provided by FINBOURNE with one specific to the user's domain.
- The standard provider.
See also: Sys.Registration
Basic usage
select * from Sys.Field where <filter-expression>;
Query parameters
Sys.Field has parameters that enable you to filter or refine a query.
To list available parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool:
select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Sys.Field' and FieldType = 'Parameter';
Data fields
By default, Sys.Field returns a table of data populated with particular fields (columns). You can return a subset of these fields.
To list fields available to return, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool:
select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Sys.Field' and FieldType = 'Column';
Note: Fields marked 'main' are returned by queries that include a caret character, for example select ^ from Sys.Field.
Examples
Note: For more example Luminesce SQL queries, visit our Github repo.
Example 1: Retrieve first 10 fields from all providers
select * from Sys.Field limit 10
Example 2: Retrieve field data types for a particular provider
select FieldName, DataType from Sys.Field where TableName = 'Drive.File'
Example 3: Retrieve number of fields returned for each provider
select TableName, count(*) FieldCount from Sys.Field
Example 4: List available parameters (and more information) for a provider
select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Lusid.Portfolio.Txn' and FieldType = 'Parameter';
Example 5: List fields available to return (and more information) for a provider
select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.Portfolio.Txn' and FieldType = 'Column';