> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://help.sigmacomputing.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://help.sigmacomputing.com/_mcp/server.

# Reference workbook control values in SQL statements

> Reference workbook control values in Sigma SQL by wrapping a control ID in curly brackets and using relevant directives within your SQL statements.

When you [write SQL statements in Sigma](/docs/write-custom-sql), you can reference the value of a control in your SQL statement by wrapping the control ID in curly brackets:

`{{my-control-id}}`

You can find the control ID in the **Settings** tab for a control. For more details about control IDs, see [Reference control values in a formula](/docs/parameters-in-workbooks).

The exact syntax depends on the output type of the control:

* [Single value](#reference-single-value-control-output-in-sql) (Most control types)
* [Min/max values](#reference-range-or-slider-control-values-in-sql) (Number range, range slider, or date range)
* [Multiple values](#reference-multiple-values-from-a-multi-select-list-in-sql) (Multi-select list control)
* [Hierarchical values](#reference-hierarchical-values-from-a-hierarchy-in-sql) (Hierarchy control)

## Limitations and warnings

* If changing the value of a control results in a statement that queries a table with a different schema, the query produces different columns than expected, resulting in errors. See [Return rows based on a schema text selector](#return-rows-based-on-a-schema-text-selector) for an example that would be impacted by this behavior.

## Reference single value control output in SQL

You can reference the selected or specified value in a specific control (the output) in a SQL statement. For a control that outputs a single value, use the syntax as follows.

### Supported control types

* Single select list
* Text input
* Text area
* Number input
* Date
* Segmented
* Drilldown
* Slider
* Checkbox
* Switch
* Top N

### Syntax

To reference the control value in a formula, enclose the control ID in double curly brackets and use the following syntax: `{{New-Control-ID}}`

### Examples

To filter the table `PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA` for orders with a quantity greater than the value specified in a number input control with control ID `number-control`, use the following example SQL:

```sql
SELECT *
from EXAMPLES.PLUGS_ELECTRONICS.PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA
where QUANTITY > {{number-control}}
```

To filter the table `FLIGHTS` for flights that were diverted based on the value of a checkbox control with control ID `checkbox-control`, use the following example SQL:

```sql
SELECT *
FROM FUN.FAA.FLIGHTS
WHERE DIVERTED = {{checkbox-control}}
```

In this example, when the checkbox is selected, it returns `True`. When the checkbox is not selected, it returns `False`. The column `DIVERTED` in the `FLIGHTS` table is a logical column that contains True or False values.

## Reference range or slider control values in SQL

You can reference the selected or specified values in number range, date range, or range slider controls in a SQL statement. For a control that outputs a range of numeric values, use the syntax as follows.

### Supported control types

* Number range
* Range slider
* Date range

### Syntax

To retrieve the minimum value of a number range or range slider control, use the following syntax:

`{{#formula [Control-ID].min}}`

To retrieve the maximum value of a number range or range slider control, use the following syntax:

`{{#formula [Control-ID].max}}`

To reference the start value of a date range control, use the following syntax:

`{{#formula [Control-ID].start}}`.

To reference the end value of a date range control, use the following syntax:

`{{#formula [Control-ID].end}}`.

### Examples

To filter the table `PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA` for orders with a quantity between the minimum and maximum values selected on a slider control with the control ID `slider-control`, use the following example SQL:

```sql
SELECT *
from EXAMPLES.PLUGS_ELECTRONICS.PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA
where QUANTITY between {{#formula [slider-control].min}} and {{#formula [slider-control].max}}
```

To filter the table `PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA` for orders between the start and end date values selected on a date range control with the control ID `current-date-range-control`, use the following example SQL:

```sql
SELECT
  *
FROM
  sigma_element ('PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA')
WHERE
  "Date" BETWEEN {{#formula [current-date-range-control].start}} AND {{#formula [current-date-range-control].end}}
```

## Reference multiple values from a multi-select list in SQL

You can reference the selected or specified values in a specific control (the output) in a SQL statement. The values in a multi-select list are returned as an array. For example:

`('apples','bananas','oranges')`

### Supported control types

* List values
* Legend

### Syntax

To reference the control value in a query, enclose the control ID in double curly brackets and use the following syntax: `{{my-control-id}}`

To reference the control value in a formula, such as to use the **ArrayLength** function to count the number of selected values, use the following syntax: `{{#formula ArrayLength([my-control-id])}}`

If the control value is output with single quotation marks, you can remove these quotation marks by prepending the keyword `#identifier` before the control ID:

`{{#identifier my-control-id}}`

For an example of the identifier directive used for dynamic schema selection, see [Return rows depending on the value of a multi-select control](#return-rows-based-on-a-schema-text-selector).

The exact syntax might be different for your connection depending on how your particular data platform interprets an array data type.

* For Snowflake, see Array in <a href="https://docs.snowflake.com/en/sql-reference/data-types-semistructured#label-data-type-array" target="_blank">Semi-structured data types</a> in the official Snowflake Documentation.
* For Databricks, see <a href="https://docs.databricks.com/en/sql/language-manual/data-types/array-type.html" target="_blank">ARRAY type</a> in the official Databricks documentation.
* For BigQuery, see <a href="https://cloud.google.com/bigquery/docs/arrays" target="_blank">Work with arrays</a> in the official Google Cloud BigQuery documentation.
* For Amazon Redshift, see <a href="https://docs.aws.amazon.com/redshift/latest/dg/r_SUPER_type.html" target="_blank">SUPER type</a> in the official Amazon Redshift Database Developer Guide.

On some connection types, you can use the `{{#formula [my-control-id]}}` syntax to reference the control value in a query, regardless of how the data platform interprets an array data type. To check if your connection supports this syntax, see [Supported data platforms and feature compatibility](/docs/region-warehouse-and-feature-support#supported-data-platforms-and-feature-compatibility).

For example:

```sql
select
  *
FROM
  APPLICATIONS.GOOGLE_ANALYTICS.EVENTS
WHERE
  TRAFFIC_SOURCE IN {{TRAFFIC_SOURCE}}
limit
  10
```

For a more detailed example, see [Return rows depending on the value of a multi-select control](#return-rows-depending-on-the-value-of-a-multi-select-control).

### Return rows depending on the value of a multi-select control

If you have a list values control called `City` and you run this query to filter the `STATIONS` table based on the values selected in the control. The example query returns all rows where the `CITY` column value is selected in the `City` control. If there are no cities selected in the `City` control, all rows are returned:

```sql
SELECT
  *
FROM
  EXAMPLES.BIKES.STATIONS
WHERE
  CASE
    WHEN {{#formula ArrayLength([City])}} = 0 THEN True
    ELSE CITY in {{City}}
  END
```

### Return rows based on a schema text selector

If you have a single-select or text control with the control-id `database` and a user attribute called `schema`, the following example SQL returns all columns from the `STATIONS` table in the specified schema:

```sql
SELECT *
FROM {{#identifier [database]}}.{{#identifier CurrentUserAttributeText('schema')}}.TRIP
```

For example, if the `database` control is set to the value `EXAMPLES`, and the `schema` user attribute is set to the value `BIKES` for the current user, the query selects all columns from the `EXAMPLES.BIKES.STATIONS` table.

When using a control or user attribute to dynamically select table, schema, or database names, the column selection in the `SELECT` statement must be valid for all tables that might be queried. In the examples in this section, the `SELECT *` statement guarantees that the column selection is available regardless of the chosen table.

Alternatively, you can parse the contents of a single control or user attribute into multiple identifiers using the [**SplitPart**](/docs/splitpart) function.

In this example, a control with control-id `database-and-schema` and contents `EXAMPLES.BIKES` is parsed into two separate identifiers:

```sql
SELECT *
FROM {{#identifier SplitPart([database-and-schema], '.', 1)}}.{{#identifier SplitPart([database-and-schema], '.', 2)}}.STATIONS
```

In this example, a user attribute called `database-and-schema` with the contents `EXAMPLES.BIKES` is parsed into two separate identifiers:

```sql
SELECT *
FROM {{#identifier SplitPart(CurrentUserAttributeText('database-and-schema'), '.', 1)}}.{{#identifier SplitPart(CurrentUserAttributeText('database-and-schema'), '.', 2)}}.STATIONS
```

## Use formulas with control values in custom SQL

You can use formulas with controls in custom SQL. This allows you to cast control values as text, concatenate control values, and reference the contents of a control in a formula.

The following functions are supported in formulas in custom SQL:

* [ArrayLength](/docs/arraylength)
* [Text](/docs/text)
* [Concat](/docs/concat)

The following [operators](/docs/operators-overview) are also supported:

* `+`
* `&`

### Syntax

To reference the control value in a formula, enclose the control ID in double curly brackets and use the following syntax:

`{{#formula function_name([Control-ID])}}`

### Count the number of selected values in a multi-select list in SQL

To count the number of selected values in a multi-select list, use the following syntax:

`{{#formula ArrayLength([Control-ID])}}`

For example, if you have a list values control called `Products`, you can use the following SQL to return the number of selected products:

```sql
SELECT {{#formula ArrayLength([Products])}}
```

### Convert a control value to text in SQL

To convert a control value to text, use the following syntax:

`{{#formula Text([Control-ID])}}`

For example, if you have a number input control called `Number-Control`, you can use the following SQL to cast the control value as text:

```sql
SELECT {{#formula Text([Number-Control])}}
```

### Concatenate control values in SQL

To concatenate control values, use the following syntax:

`{{#formula Concat([Control-ID-1], [Control-ID-2], ...)}}`

For example, if you have two text input controls called `First-Name` and `Last-Name`, you can use the following SQL to concatenate the control values:

```sql
SELECT {{#formula concat([First-Name], ' ', [Last-Name])}}
```

### Use multiple formulas with control values in SQL

You can use multiple formulas with control values in a single SQL statement. In the example below, the `day-in-november` control is a number range control that allows users to select a range of days in November of 2024. The SQL statement concatenates the starting and ending days as text with the month and year to create a date range.

```sql
SELECT *
FROM EXAMPLES.PLUGS_ELECTRONICS.PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA
WHERE
DATE BETWEEN 
  to_date({{#formula concat('2024-11-', text([day-in-november].min), ' 00:00:00')}})
AND
  to_date({{#formula concat('2024-11-', text([day-in-november].max), ' 23:59:59')}})
```

This example combines the concat and text functions with the .min and .max syntax to create a date range. Each range uses only one block of the `{{#formula ...}}` syntax, as only one is needed per output.

## Reference hierarchical values from a hierarchy in SQL

Referencing the selected values of a hierarchy control in a SQL statement is not yet supported. See [Hierarchy control](/docs/intro-to-control-elements#hierarchy).