Skip to main content

# OPTIONAL_FILTER

Declares a filter that a reader switches on.

{/* Written by apps/landing-page/scripts/docs/write_kater_functions.py from the compiler's function registry, kater_sql/function_docs.py and scripts/docs/sql-language.md. Do not edit it by hand: change those, then run the script. */}

**Family:** [Filters](https://kater.ai/docs/sql-reference/functions/filters/)

When it is off, the query keeps every row it would keep without it.

## Syntax

```sql
WHERE
  OPTIONAL_FILTER('name', condition, label='Label')
```

## Arguments

| Argument | Description |
| --- | --- |
| `name` | The filter's name, in quotes. Readers and the inputs of `kater run` turn it on by this name. |
| `condition` | The condition the filter applies, such as `KATER__IS_ONE_OF(orders.region, 'AMER', 'EMEA')`. It can read a parameter, such as `:is_one_of__region`. |
| `label` | The name a reader sees, such as `'Region'`. |

## Returns

Nothing of its own. When it is on, the query keeps only the rows where the condition is TRUE.

## Usage notes

- Write it in a workspace root or a query, as a direct `AND` condition of WHERE.
- A parameter that it reads is declared in the file's `parameters:`. See [Parameters](https://kater.ai/docs/sql-reference/parameters/).
- A Sandbox run switches each optional filter on, with each parameter at its default value.

## Example

This Sandbox query reads the sample shop's orders. The table shows the rows that Kater gave.

The Sandbox runs the statement with the filter on, so only AMER and EMEA show.

```sql {6}
SELECT
  orders.region AS "Region",
  EVAL(orders.count_distinct__order_id) AS "Orders"
FROM kater_dataset.orders AS orders
WHERE
  OPTIONAL_FILTER('is_one_of__region', KATER__IS_ONE_OF(orders.region, 'AMER', 'EMEA'), label='Region')
GROUP BY
  orders.region
ORDER BY
  1
```

| Region | Orders |
| --- | --- |
| AMER | 728 |
| EMEA | 777 |

## See also

- [Filters and parameters](https://kater.ai/docs/model/filters-and-parameters/)
- Other functions in [Filters](https://kater.ai/docs/sql-reference/functions/filters/): [`REQUIRED_FILTER`](https://kater.ai/docs/sql-reference/functions/required_filter/)

On this page

Syntax
Arguments
Returns
Usage notes
Example
See also

Kater SQL Functions Filters

OPTIONAL_FILTER

Declares a filter that a reader switches on.

Family: Filters

When it is off, the query keeps every row it would keep without it.

Syntax

1
WHERE
2
  OPTIONAL_FILTER('name', condition, label='Label')

Arguments

Argument	Description
`name`	The filter’s name, in quotes. Readers and the inputs of `kater run` turn it on by this name.
`condition`	The condition the filter applies, such as `KATER__IS_ONE_OF(orders.region, 'AMER', 'EMEA')`. It can read a parameter, such as `:is_one_of__region`.
`label`	The name a reader sees, such as `'Region'`.

Returns

Nothing of its own. When it is on, the query keeps only the rows where the condition is TRUE.

Usage notes

Write it in a workspace root or a query, as a direct AND condition of WHERE.
A parameter that it reads is declared in the file’s parameters:. See Parameters.
A Sandbox run switches each optional filter on, with each parameter at its default value.

Example

This Sandbox query reads the sample shop’s orders. The table shows the rows that Kater gave.

The Sandbox runs the statement with the filter on, so only AMER and EMEA show.

1
SELECT
2
  orders.region AS "Region",
3
  EVAL(orders.count_distinct__order_id) AS "Orders"
4
FROM kater_dataset.orders AS orders
5
WHERE
6
  OPTIONAL_FILTER('is_one_of__region', KATER__IS_ONE_OF(orders.region, 'AMER', 'EMEA'), label='Region')
7
GROUP BY
8
  orders.region
9
ORDER BY
10
  1

Region	Orders
AMER	728
EMEA	777

See also

Filters and parameters
Other functions in Filters: REQUIRED_FILTER