Luminesce queries are written in the SQL dialect of SQLite, with the syntactical extensions and limitations described below. See which tools are available to write queries.
Note a query can be written either for a data provider or a direct provider, and there are some syntactical differences between them. In particular, direct providers have an arbitrary syntax that may differ for each.
Summary
Luminesce supports most of the syntax of the SQLite SELECT statement. It does not support data definition language (DDL) syntax for creating or modifying database objects.
- Common table expressions (CTE) are not supported in Luminesce
SELECT
statements but a re-usable variable syntax is available instead. TYPES
statements can be used before LuminesceSELECT
statements to force type conversion on columns.FOR-EACH
loops can be emulated using APPLY statements.- A
WAIT
modifier is available to pause query execution. - A number of common SQL functions are supported, and we've supplemented these with sets of custom functions and statistical functions.
- A number of PRAGMA statements specific to Luminesce are supported.
- Dates must be encapsulated in
#
rather than'
characters, so for example#2023-11-15 15:59:59#
. - Strings must be encapsulated in
'
rather than"
characters, so for examplePortfolioScope = 'Finbourne-Examples'
. - Multiple lines can be commented out using
/*
and*/
characters at the beginning and end respectively.
SELECT statement examples | Explanation |
select * from Some.Provider | Selects all the columns from Some.Provider |
select x, y from Some.Provider | Selects the X and Y columns |
select * (except x, y) from Some.Provider | Selects all the columns except X and Y. Can use the - character in place of except |
select ^ from Some.Provider | Selects the main columns (those considered most important by the provider author) |
select ^ (except x, y) from Some.Provider | Selects the main columns except X and Y |
select * from Some.Provider limit 5 | Returns only the first 5 records |
For a Some.Provider
that is authored by FINBOURNE, the fields (columns) available to return or filter can be determined by running the following query:
select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Some.Provider' and FieldType = 'Column';
Parameters
Data providers may have parameters that can be used to filter a Luminesce query as part of a standard WHERE
clause.
Note: Direct providers do not support parameters.
You must assign values to parameters unambiguously (not under an OR
clause), and only with the =
operator. For example:
select * from Some.Provider where SomeParameter = 12
For a Some.Provider
that is authored by FINBOURNE, available parameters can be determined by running the following query:
select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Some.Provider' and FieldType = 'Parameter';
Variables
You can use variables to create and populate arbitrary tables of data that can then be used as part of a Luminesce query for either a data provider or a direct provider.
This is similar to a combination of the standard SQL CREATE TABLE...INSERT INTO
statements.
Variable type | Explanation | Examples |
@tablevariable | Stores a table of any size | @abc = select TableName, FieldName from Sys.Field order by 1, 2; |
@@scalarvariable | Only stores a scalar value (that is, the SELECT statement must resolve to exactly one column and one row of data) | @@abc = select strftime('%Y%m%d', 'now'); @@portfolioscope = select 'Finbourne-Examples'; |
Type conversion
You can use the TYPES
statement before any SELECT
statement in a Luminesce query to force type conversion on columns.
Valid types are BIGINT
, INT
, DOUBLE
, DECIMAL
, BOOLEAN
, TEXT
, DATE
, TIME
and DATETIME
.
Example | Explanation |
TYPES bigint, int, text, boolean; SELECT 1, 2, 3, 4; | Returns columns as Int64, Int32, String and Boolean types respectively. |
TYPES bigint, int, , boolean; SELECT 1, 2, 3, 4; | Returns columns 1, 2 and 4 as Int64, Int32 and Boolean types respectively, leaving column 3 to be inferred in the normal way. |
TYPES text; SELECT 1, 2, 3, 4; | Returns column 1 as text and leaves all the other columns to be inferred in the normal way. |
TYPES double; SELECT SUM(Cost) as X FROM Products; | Returns the result of the aggregate function as a double. |
@data = TYPES text; values ('018802','NR',#2000-07-13T00:00:00Z#,'LegalEntity','moodysLongRating'); | Returns column 1 as text, leaves all the other columns to be inferred in the normal way, and assigns the result to a table variable. |
FOR-EACH loops
You can use the CROSS APPLY
or OUTER APPLY
statement to emulate a FOR-EACH
loop. This is useful to execute a Luminesce query in parallel on a range of parameter inputs.
Statement | Explanation |
CROSS APPLY | Similar to INNER JOIN , in that only records that have matching values on both sides are returned. |
OUTER APPLY | Similar to LEFT OUTER JOIN , in that all records on the left side are returned, even when there are no matching records on the right side. |
For example, the following query uses OUTER APPLY
with the Lusid.Portfolio.Holding provider to generate a holdings report for each of the last 3 days:
@days = select date('now', '-' || Value || ' days') as dt
from Tools.[Range] where number = 3 -- go back N days from today
and Start = 0; -- subtract 0 days, to include today (1 would exclude today)
select d.dt, results.*
from @days d
outer apply (
select h.^
from lusid.portfolio.holding h
where h.EffectiveAt = d.dt and h.PortfolioCode = 'test-portfolio'
) results;
Note the following:
- There is no
ON
clause for eitherCROSS APPLY
orOUTER APPLY
. - You must alias all tables inside the
SELECT
statement of eitherCROSS APPLY
orOUTER APPLY
. In the example above,h
is the alias for the table returned byLusid.Portfolio.Holding
. - You must explicitly specify columns in the
SELECT
statement outside eitherCROSS APPLY
orOUTER APPLY
if you then use those columns in aWHERE
condition inside the statement. The example above hash.effectiveAt = d.dt
inside the statement, soselect d.dt
must be explicitly specified outside it. Specifyingselect d.*
outside would result in an error.
Pausing query execution
You can use the WAIT
modifier at any point in a query to wait for all the preceding code to execute before continuing, and optionally for a further specified number of seconds.
For example, if you create a view you may want to wait 5 seconds for it to appear in the Luminesce catalog before querying it:
@model_portfolios_view = use Sys.Admin.SetupView --provider=Test.Example.TestHoldings ---- select PortfolioScope, PortfolioCode, BaseCurrency from Lusid.Portfolio where PortfolioType = 'Reference' enduse; --- Create the Test.Example.TestHoldings view @result = select * from @model_portfolios_view; --- Query the view, waiting 5 seconds for Luminesce to register it select * from Test.Example.Testholdings wait 5 limit 1;
PRAGMA statements
You can use the PRAGMA statements in the following table to override default Luminesce settings for a particular query. For example, to allow a query to run for 30 minutes before timing out, specify:
pragma TimeoutSec = 1800;
PRAGMA | Explanation |
MaxApplyParallelization = <number> | Defines the maximum number of parallel provider calls within a CROSS APPLY or OUTER APPLY statement in a query. Defaults to 5. |
TimeoutSec = <seconds> | Specifies a number of seconds to wait before a query times out. |
QueryName = <name> | Names a query. |
TypeDeterminationRowCount = <rows> | Specifies the number of rows to use to determine the data type on returned data sets (defaults to 500). Note that for views it is better to prefer the ~<DataType> syntax. |
WebApiCacheForSeconds = <seconds> | For use with the Luminesce /api/SqlBackground/* REST APIs in the Sql Background Execution collection. Re-uses the results from a previous character-identical query for up to the number of seconds when run by the same user. This must be specified on the 1st and 2nd+ usages of the query. |