| Type | Read/write | Author | Availability |
| Data provider | Read | Finbourne | Provided with LUSID |
The Lusid.UnitResult.StructuredResult provider enables you to write a Luminesce SQL query that retrieves one or more documents from LUSID's structured result store (SRS).
Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and read SRS data in LUSID. This should automatically be the case if you are the domain owner.
See also: Lusid.UnitResult.StructuredResult.Writer, Lusid.UnitResult.AtomisedResult
Basic usage
@lookup_table = <select-statement>;
select * from Lusid.UnitResult.StructuredResult where toLookUp = @lookup_table and <filter-expression>
Query parameters
Lusid.UnitResult.StructuredResult has parameters that help you construct a valid table of data to look up.
Note: The toLookUp parameter is mandatory. Use at least the following data fields in your select statement to identify the document(s) to look up:
| Data field | Explanation |
Scope | The scope of the document to look up. |
Code | The code of the document. |
Source | The source of the document. |
EffectiveAt | The effective date of the document in LUSID. |
ResultType | The result type of the document. This might be one of the built-in LUSID types (UnitResult/Analytic, UnitResult/Portfolio, UnitResult/Holding, UnitResult/Grouped), or it could be a custom type (any string). |
To list all 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 = 'Lusid.UnitResult.StructuredResult' and FieldType = 'Parameter';
Data fields
By default, Lusid.UnitResult.StructuredResult returns a table of data populated with particular fields (columns). You can return a subset of these fields.
To list all 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 = 'Lusid.UnitResult.StructuredResult' and FieldType = 'Column';
Note: Fields marked 'main' are returned by queries that start select ^ from Lusid.UnitResult.StructuredResult...
Errors
We recommend examining the results of every query using the Error field.
This field is populated if Lusid.UnitResult.StructuredResult cannot retrieve a document for some reason. For example, the Error field reveals that this query:
@lookup_table = select 'srs-accrual' as Scope, 'accrued-results' as Code, 'Client' as Source, 'UnitResult/Analytic' as ResultType;
select Error from Lusid.UnitResult.StructuredResult where toLookUp = @lookup_table;
...fails because an EffectiveAt date has not been provided in the lookup table.
Examples
Note: For more example Luminesce SQL queries, visit our Github repo.
Example 1: Retrieve a particular document
Note that the Scope, Code, Source and ResultType values are case-sensitive.
@lookup_table = select 'SRS-Accrual' as Scope, 'accrued-results' as Code, 'Client' as Source, 'UnitResult/Analytic' as ResultType,
#2022-03-07# as EffectiveAt;
select * from Lusid.UnitResult.StructuredResult where toLookUp = @lookup_table;
Example 2: Retrieve multiple documents with different codes and sources
@documents = values
('accrued-results', 'Bloomberg'),
('instrumentaccrued-results', 'Refinitiv');
@lookup_table = select 'srs-accrual' as Scope, column1 as Code, column2 as Source, 'UnitResult/Analytic' as ResultType,
#2022-03-07# as EffectiveAt from @documents;
select * from Lusid.UnitResult.StructuredResult where toLookUp = @lookup_table;