---
title: "Understanding the Luminesce SQL query syntax"
slug: "understanding-the-luminesce-sql-query-syntax"
tags: ["luminesce ", "sql"]
updated: 2026-10-20T07:35:38Z
published: 2026-10-20T07:35:38Z
canonical: "support.lusid.com/understanding-the-luminesce-sql-query-syntax"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.lusid.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Understanding the Luminesce SQL query syntax

Luminesce SQL queries are written in the SQL dialect of [SQLite](https://www.sqlite.org/lang.html), with the syntactical extensions and limitations described below. [See which tools are available to write queries](/v1/docs/what-tools-are-available-to-write-luminesce-queries).

Note a query can be written either for a [data provider or a direct provider](/v1/docs/what-are-a-data-provider-and-a-direct-provider), and there are some syntactical differences between them. In particular, direct providers have an [arbitrary syntax](/v1/docs/what-are-a-data-provider-and-a-direct-provider#direct-provider) that may differ for each.

## Summary

Luminesce SQL supports most of the syntax of the [SQLite SELECT statement](https://www.sqlite.org/lang_select.html). 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](/v1/docs/understanding-the-luminesce-sql-query-syntax#variables) is available instead.
- `TYPES` statements can be used before Luminesce `SELECT` statements to force [type conversion](/v1/docs/understanding-the-luminesce-sql-query-syntax#type-conversion) on columns.
- `FOR-EACH` loops can be emulated using [APPLY statements](/v1/docs/understanding-the-luminesce-sql-query-syntax#foreach-loops).
- Most types of [JOIN clause](/v1/docs/understanding-the-luminesce-sql-query-syntax#join-clauses) are supported.
- A `WAIT` modifier is available to [pause query execution](/v1/docs/understanding-the-luminesce-sql-query-syntax#pausing-query-execution-using-wait).
- A number of [common SQL functions](/v1/docs/supported-functions-in-luminesce-sql-queries) are supported, and we've supplemented these with sets of [custom functions](/v1/docs/supported-functions-in-luminesce-sql-queries#general-custom-functions) and [statistical functions](/v1/docs/supported-functions-in-luminesce-sql-queries#statistical-custom-functions).
- A number of [PRAGMA statements](/v1/docs/understanding-the-luminesce-sql-query-syntax#pragmas) specific to Luminesce are supported.
- [Server table variables](/v1/docs/understanding-the-luminesce-sql-query-syntax#ss) let you cache query results and reuse them in later queries.
- A number of ways to [convert to and from JSON](/v1/docs/understanding-the-luminesce-sql-query-syntax#converting-to-and-from-json) are supported.
- [Parameterised queries](/v1/docs/understanding-the-luminesce-sql-query-syntax#parameterised-queries) can be run using `:<unique-name>:<default-value>`.
- Dates must be encapsulated in `#` rather than `'` characters, so for example `#2023-11-15 15:59:59#` (though other date formats [can also be understood](https://www.sqlite.org/quirks.html#no_separate_datetime_datatype)).
- Strings must be encapsulated in `'` rather than `"` characters, so for example `PortfolioScope = 'Finbourne-Examples'`. Note SQLite interprets a column name encapsulated in `"` characters as a string literal if it doesn't match a valid column name. For this reason, we recommend encapsulating column names in `[]` characters. [Read more on this in the SQLite documentation.](https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted)
- Multiple lines can be commented out using `/*` and `*/` characters at the beginning and end respectively.

[See the full list of valid keywords](/v1/docs/luminesce-sql-keywords) (both inherited from SQLite and proprietary to FINBOURNE).

| **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:

```sql
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, although they do have the `WITH` syntax that provides much the same functionality.

You must assign values to parameters unambiguously (not under an `OR` clause), and only with the `=` operator. For example:

```sql
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:

```sql
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 result, passed into providers, cached in a variety of ways, exported, and more.

This is similar to a combination of the various statements supported by other SQL engines, for example:

- `CREATE [TEMP] TABLE...INSERT INTO` statements in PostgreSQL
- `DECLARE @t TABLE(…)` in Microsoft SQL Server

| **Variable type** | **Explanation** | Lifetime | **Examples** |
| --- | --- | --- | --- |
| `@tablevariable` | Stores a table of any size. | The current query | `@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). | The current query | `@@abc = select strftime('%Y%m%d', 'now');` `@@portfolioscope = select'Finbourne-Examples';` |
| `@!tableVariable` | Stores a limited-size table variable in persistent storage. You can use it in the current query and reuse it in later queries for a limited time. Only the user who created it can access it. [Read more.](/v1/docs/understanding-the-luminesce-sql-query-syntax#ss) | 8 hours (default) Use `PRAGMA VariableCacheMins` to change that | `@!holdings = select * from Lusid.Portfolio.Holding limit 1000;` |

### Server table variables

A server table variable caches the result of a query so you can reuse it in later queries. This saves you retrieving the same data from LUSID each time, which is particularly useful when you’re exploring data or building up a complex query.

For example, the following query retrieves 1,000 holdings from LUSID and caches them as `@!holdings`:

```sql
@!holdings = select * from Lusid.Portfolio.Holding limit 1000;
select * from @!holdings;
```

Later queries can select from `@!holdings` directly, which is much faster than retrieving the holdings from LUSID again.

Note the following:

- Only the user who ran the query that created a server table variable can access it.
- A server table variable expires after 8 hours by default. You can use a `PRAGMA VariableCacheMins = N;` to set a lifetime of between 1 minute and 24 hours.
  - This will apply to all the server table variables in the query.
  - The underlying data wil be cleared from persistent storage shortly after this time period elapses.
  - You will still see mention of this variable in the catalog and code-completion for a few hours afterwards, before all mention of it is removed. It clearly shows as expired in its description. This is to remind you that it did exist, but has expired and help with repopulating it if needed, by showing you the SQL that created it originally.
- There is a size limit per variable. If the data exceeds it, Luminesce displays a warning and you can’t read the variable in later queries, not will it be shown in the catalog / code-completion at all.
  - With `?=` it simply means calculating the variable each time the query is run and having no cache.
- A new server table variable may take a moment to become available for reuse.

> [!NOTE]
> Other ways to cache and share data
> 
> If the limits above don’t suit your use case, you can use the Client Data Store (see [Sql.Db](/v1/docs/readwrite-to-sql-databases-sqldbmine)) to create your own providers backed by a Postgres database. You manage the data’s lifetime and permissions yourself, and you can store larger volumes of data and share it with other users.
> 
> 
> 
> Note that the Client Data Store is slower to populate, has a fixed shape that you must update manually if your data changes, and requires macros when you first set up a provider so that Luminesce can retrieve data efficiently. There are many ways to do this in Luminesce; you can, for example, use `Drive.SaveAs` and `Drive.Csv` to write data to, and read from, files in [Drive](/v1/docs/drive).

#### Retrieving data only when it’s missing

You can use the `?=` assignment operator to reuse cached data if it exists, and retrieve it only if it doesn’t. This operator is available only for server table variables. For example:

```sql
@!holdings ?= select * from Lusid.Portfolio.Holding limit 1000;
select * from @!holdings;
```

Luminesce retrieves data from the underlying providers only if `@!holdings`:

- Hasn’t been cached yet
- Failed to be cached to due size limits
- Was created by different SQL
- Has expired

In every case, the query returns the data. This is useful in views that you call using [CROSS APPLY](/docs/understanding-the-luminesce-sql-query-syntax#foreach-loops), because caching part of the work means Luminesce doesn’t have to repeat it hundreds of times. Due to concurrency of APPLY processing it may still be calculated a handful of times before being able to be reused, however.

To update an existing query, add `!` to the variable name and `?` before the assignment operator.

Many use cases are simply inappropriate for using any form of cache, for example a query where the data would vary by input parameter (thus potentially caching entirely the wrong data for the next query). It is up to the query author to be sure a cache is appropriate for the cases when they use it.

#### Finding server table variables in the Luminesce catalogue

`@!tableVariables` appear in your catalog alongside other tables, including in the catalogue explorer and code completion helpers.

Luminesce also carries lineage forward, so you can see the SQL that created a server table variable and the full lineage behind that SQL.

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image-5LI6M4CS.png?sv=2026-02-06&spr=https&st=2026-11-04T01%3A47%3A09Z&se=2026-11-04T02%3A47%3A09Z&sr=c&sp=r&sig=GRzbgeRzuOpIGahgQcYQMpSB8vKsO41HrVS%2BAZams2E%3D)

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image-AAXOF4G4.png?sv=2026-02-06&spr=https&st=2026-11-04T01%3A47%3A09Z&se=2026-11-04T02%3A47%3A09Z&sr=c&sp=r&sig=GRzbgeRzuOpIGahgQcYQMpSB8vKsO41HrVS%2BAZams2E%3D)

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image-YQQLNWR1.png?sv=2026-02-06&spr=https&st=2026-11-04T01%3A47%3A09Z&se=2026-11-04T02%3A47%3A09Z&sr=c&sp=r&sig=GRzbgeRzuOpIGahgQcYQMpSB8vKsO41HrVS%2BAZams2E%3D)

## Type conversion

You can use the `TYPES` statement before any `SELECT` statement in a Luminesce query to force type conversion on columns. Note that this overrides the default SQLite behaviour of [flexible typing](https://www.sqlite.org/quirks.html#flexible_typing).

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](/v1/docs/understanding-the-luminesce-sql-query-syntax#variables). |

## 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](/v1/docs/lusidportfolioholding) provider to generate a holdings report for each of the last 3 days:

```sql
@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.* -- outer query
        from @days as d
        outer apply (
        select h.^ -- inner query
        from lusid.portfolio.holding as h
        where h.EffectiveAt = d.dt
        and h.PortfolioScope = 'EMEA'
        and h.PortfolioCode = 'Equities'
        ) as results ;
```

Note the following:

- Tables used in the inner or outer queries must be aliased, so for example `as d` in the outer query above and `as h` in the inner query.
- Columns used in the `where` clause of the inner query must be explicitly selected in the outer query, so for example `d.dt` in the inner must be referenced as `select d.dt` in the outer. Referencing as `select d.*` would result in an error.
- There must be whitespace before the closing `;` character, for example `as results ;`
- [Variables](/v1/docs/understanding-the-luminesce-sql-query-syntax#variables) (whether `@table` or `@@scalar`) are not expanded in the inner query.
- There is no `ON` clause for either `CROSS APPLY` or `OUTER APPLY`.

## JOIN clauses

Luminesce supports the following JOIN clauses: `INNER JOIN`, `LEFT (OUTER) JOIN`, `CROSS JOIN`, `FULL (OUTER) JOIN`.

Luminesce does not currently support `RIGHT (OUTER) JOIN`.

## Pausing query execution using WAIT

You can use the `WAIT` modifier immediately after any `select * from x` statement in a query to wait for code to execute before continuing.

For example, if you [create a custom view](/v1/docs/using-sysadminsetupview-to-create-custom-views) you may want to wait 5 seconds for it to appear in the Luminesce catalog before querying it:

```sql
@model_portfolios_view = use Sys.Admin.SetupView
        --provider=Test.Example.TestHoldings
        ----
        select PortfolioScope, PortfolioCode, BaseCurrency
        from
        Lusid.Portfolio
        where
        PortfolioType = 'Reference'
        enduse;
        --- Create the custom view and then wait 5 seconds
        @result = select * from @model_portfolios_view wait 5;
        --- Query the custom view, having waited for Luminesce to register it
        select * from Test.Example.Testholdings limit 1;
```

## PRAGMAs

You can use one or more of the PRAGMA statements in the following table at any point in a query to override default Luminesce settings for that query. For example, to allow a query to run for 30 minutes before timing out, specify:

PRAGMA names are not case-sensitive. When a query uses a view, some PRAGMAs also apply to the queries inside that view, combined with any the view sets itself: for example, `MaxApplyParallelization` and `MaxParallelVariableCalculations` take the lower of the two values, and `TimeoutSec`, `TypeDeterminationRowCount`, `ProviderRetryNonResponsiveAttempts` and `MaxJoinFilterKeys` take the higher.

```sql
pragma TimeoutSec = 1800;
```

| **PRAGMA** | **Explanation** |
| --- | --- |
| `DryRun = True \| False` | When `True`, parses the query, verifies access to provider(s) and, where possible, returns dummy data so you can examine the shape. However, does not execute the query, so (for example) write providers do not modify data. Designed for use in CI/CD pipelines where there may be downstream implications. |
| `MaxApplyParallelization = <number>` | Defines the maximum number of parallel provider calls within a [CROSS APPLY or OUTER APPLY](/v1/docs/understanding-the-luminesce-sql-query-syntax#foreach-loops) statement in a query. Defaults to 5. |
| `AutoIndexForJoins = True \| False` | Defaults to `False`. When `True`, auto-creates indexes on the returns of variable calculations and provider results based on join clauses that use them. This can potentially optimise any join, but improvements can be seen most notably on `FULL [OUTER] JOINS` with data of a moderate size. Note: - Currently, this option can only be enabled on the entire query, not on individual joins. - Use of this PRAGMA may reduce performance in some cases. |
| `MaxParallelVariableCalculations = <number>` | Defines the maximum number of variable calculations that may be performed in parallel in a query. No limit by default. Note if you experience a `"database is locked"` error, adding `pragma MaxParallelVariableCalculations = 1` to your query may help to resolve the issue. |
| `ProviderRetryNonResponsiveAttempts = <number>` | Specifies the number of times to retry a provider if it is determined to be non-responsive. Defaults to 1. |
| `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](/v1/docs/understanding-the-luminesce-sql-query-syntax#type-conversion) on returned data sets (defaults to 500). Note that for [custom views](/v1/docs/using-sysadminsetupview-to-create-custom-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](https://www.lusid.com/honeycomb/swagger/index.html) 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. |
| `WebApiCacheOnlyOnSuccess = True \| False` | Use with `WebApiCacheForSeconds`. When `True`, a query that fails is not kept in the cache, so the next run executes it again rather than reusing the failure. |
| `QueryDiagnostics = <flag> [, <flag>...]` | Specifies flags that can be given to the query coordinator to perform during execution for additional diagnostics. The following flags can be specified: - `TableVariables` dumps the column structure and first few rows of every [table variable](/v1/docs/understanding-the-luminesce-sql-query-syntax#variables) that requires calculation immediately after it is calculated. - `ScalarVariables` dumps the value of every [scalar variable](/v1/docs/understanding-the-luminesce-sql-query-syntax#variables) that requires calculation immediately after it is calculated. - `DependencyTree` outputs the dependency tree within the query. - `MergeTable` outputs diagnostics whenever tables are being merged. - `LineageTree` outputs the lineage tree of the query, if available. - `All` performs all possible additional diagnostics which are enabled through using this PRAGMA. |
| `MaxJoinFilterKeys = <number>` | Overrides the default limit of 100,000 filter keys passed down to the underlying engine before scanning the table instead. Some joins need several keys per row. |
| `VariableCacheMins = <minutes>` | Specifies how many minutes the [server table variables](/v1/docs/understanding-the-luminesce-sql-query-syntax#variables) (`@!x = select ...`) this query creates are kept for. Defaults to 480 (8 hours); can be between 1 and 1440 (24 hours). |

## Converting to and from JSON

Luminesce supports the functions exposed by the [JSON1 extension library](https://www.sqlite.org/json1.html).

You can use the dedicated [Tools.JsonExpand](/v1/docs/toolsjsonexpand) provider to emulate the SQLite `json.tree` function and convert a JSON document into a table of data objects.

To convert a table of data objects into JSON, you can use the SQLite `json_group_array` and `json_object` functions. This is required when using a provider that has a field with a `Json` suffix, for example the `PeriodsJson` field in the [Lusid.Instrument.SimpleCashFlowLoan.Writer](/v1/docs/lusidinstrumenttypewriter-providers) provider. The `PeriodsJson` field is a text field that expects a flattened array of JSON objects as a value, each representing a loan period with `PaymentDate`, `Notional` and `InterestAmount` fields. For example:

```sql
@periods = values
        (#2022-03-01#, 100, 5),
        (#2022-06-01#, 90, 4),
        (#2022-09-01#, 50, 4.5),
        (#2022-12-01#, 30, 2.0);

        @@periodsArray = select
        json_group_array(
        json_object(
        'PaymentDate', column1,
        'Notional', column2,
        'InterestAmount', column3
        )
        ) from @periods;

        @table_of_data = select 'MySimpleCashFlowLoan' as DisplayName, 'SimpleCashFlowLoan' as ClientInternal,
        #2022-01-01# as StartDate, #2023-01-01# as MaturityDate, 'GBP' as DomCcy, @@periodsArray as PeriodsJson;

        select * from Lusid.Instrument.SimpleCashFlowLoan.Writer where ToWrite = @table_of_data;
```

## Parameterised queries

[Most tools](/v1/docs/what-tools-are-available-to-write-luminesce-queries) you can use to run Luminesce queries support parameterisation using the following syntax:

- For a single user-specified value: `:<unique-name>:<default-value>`
- For a fixed list of allowed values: `:<unique-name>:{<value-1>, <value-2> [, <value-3>...]}`
- For a list of allowed values or a user-specified value (note the `*`): `:<unique-name>:{<value-1>, <value-2> [, <value-3>...] *}`

> [!NOTE]
> Note
> 
> If you want spaces in the unique name, you must encapsulate it in square brackets.

Consider the following example:

```sql
select * from Lusid.Portfolio.Txn
        where PortfolioScope = 'Finbourne-Examples'
        and PortfolioCode = :[Choose fund]:{'UK-Equities','US-Equities'} -- User must choose one of these values
        and ShowNonActive = :[Show everything]:True
        and TransactionDate > :After:#2020-07-01#
        and TotalConsideration > :[For more than]:1000
        and TradeCurrency = :[Trade ccy]:{'GBP','USD','EUR' *} -- User can choose one of these values, or specify their own

        -- You can "type" a parameter, yet not provide a default value (the query otherwise derives types from the default value):
        and PortfolioCode != :[For more than 3]:null -- A text parameter with no default, stated explicitly
        and PortfolioCode != :[For more than 4]: -- A text parameter with no default (parameter type defaults to TEXT)
        and TotalConsideration > :[For more than 2]:INT -- A number parameter with no default
        
        -- You can use queries to define options also, for example:
        --   Only show distinct results
        --   Only select one column
        and TradeCurrency = :[Trade ccy 2]:{(select ISOCurrencyCode from CoppClark.Currencies order by 1)*} 
                            -- Currencies without a default, any text allowed
        and TradeCurrency = :[Trade ccy 4]:{'USD' (select distinct ISOCurrencyCode from CoppClark.Currencies order by 1)*} 
                            -- Currencies with a default, any text allowed
        and TradeCurrency = :[Trade ccy 3]:{'USD' (select distinct ISOCurrencyCode from CoppClark.Currencies order by 1 limit 5)} 
                            -- Currencies with a default, only items in the list are allowed
        and TotalConsideration > :[For more than 5]:{INT (select Value from [Tools.Range] where Start = 100 and Number = 100 and Step = 5)}
                            -- No default value, a number value from only items in the list
```

When this query is run from either the **Data Virtualisation > Query Editor** or **Saved Queries** screen in the LUSID web app, the user is prompted to enter the following values at run time:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image(831).png?sv=2026-02-06&spr=https&st=2026-11-04T01%3A47%3A09Z&se=2026-11-04T02%3A47%3A09Z&sr=c&sp=r&sig=GRzbgeRzuOpIGahgQcYQMpSB8vKsO41HrVS%2BAZams2E%3D)

Note if the tool you use to run a query does not support parameterisation, the query uses the default value.