Tools.Range

The Tools.Range provider enables you to write a Luminesce query that provides a simple range of values.

Note: The LUSID user running the query must have sufficient access control permissions to use this provider. This should automatically be the case if you are the domain owner.

You can use this provider in conjunction with other providers to generate a range of values for further querying, see example 3.

Basic usage

select * from Tools.[Range] where <filter-expression>

Query parameters

Tools.Range 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 = 'Tools.Range' and FieldType = 'Parameter'; 

Data fields

By default, Tools.Range returns a table of data populated with particular fields (columns). You can return just a subset of these fields if you wish.

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 = 'Tools.Range' and FieldType = 'Column'; 

Note: Fields marked 'primary key' are returned by queries that start select ^ from Tools.[Range]...

Examples

Example 1: Generating a default range of values

select value from Tools.[Range]

The table of data returned by the query shows one column with values from 1 to 100.

Example 2: Generating a range of 4 values which start from 5 and increment by 3

select * from Tools.[Range] where start = 5 and number = 4 and step = 3 

The table of data returned by the query looks like this:

Example 3: Generating a range of dates to fetch LUSID logs from

In this example, Tools.Range is used to generate dates for the previous three days, which are then passed into the Lusid.Logs.AppRequest provider to fetch all GetValuation request logs for each day in this time range.

@days = 
select 
    date('now', '-' || Value || ' days') as datetime1,
    date('now', '-' || Value || ' days', '+1 days') as datetime2
from Tools.[Range] where number = 3
and Start = 0;
select 
    d.datetime1, 
    d.datetime2, 
    r.Timestamp,
    r.Duration,
    r.StatusCode
from 
    @days as d 
    OUTER APPLY 
    (
        select 
            ar.Timestamp, ar.Duration, ar.StatusCode 
        from 
            Lusid.Logs.AppRequest as ar
        where 
            ar.StartAt = d.datetime1
            and ar.EndAt = d.datetime2
            and ar.Application = 'lusid'
            and ar.EventType = 'Completed'
            and ar.Method = 'GetValuation'
    ) r
 ;

The table of data returned by the query looks like this: