Usage patterns in SQL

Parameters aren't a templating language; they're embedded as regular SQL values. Express conditional branching or repetition using SQL syntax such as CASE or IN. See Escaping and dynamic references for details on escaping.

TIP

The {{label}} notation below is used for explanation. On screen, it's shown as a labeled reference chip.

Optional filter

When a value is unset, the zero value is expanded. By removing the filter when the value equals the zero value, you can make it function as "fetch all" when unset.

SELECT order_id, product_name, ordered_at
FROM orders
WHERE
  -- Start date: don't filter by the lower bound if it's the zero value ('1970-01-01')
  CASE WHEN {{start_date}} = '1970-01-01' THEN TRUE ELSE ordered_at >= {{start_date}} END
  -- End date: don't filter by the upper bound if it's the zero value ('1970-01-01')
  AND CASE WHEN {{end_date}} = '1970-01-01' THEN TRUE ELSE ordered_at <= {{end_date}} END
  -- Product name: don't filter by product name if it's the zero value ('')
  AND CASE WHEN {{product_name}} = '' THEN TRUE ELSE product_name LIKE '%{{product_name}}%' END

You can also write it as follows, with the same effect.

SELECT * FROM orders
WHERE 1 = 1
  AND (order_date >= {{start_date}} OR {{start_date}} = '1970-01-01')
  AND (status = {{status}} OR {{status}} = '')

Branching by value

Switch the selected column or calculation based on a parameter's value.

SELECT
  order_id,
  CASE {{region}}
    WHEN 'EU' THEN amount * 1.20
    ELSE amount
  END AS final_price
FROM orders

SELECT
  CASE {{granularity}}
    WHEN 'daily' THEN DATE_TRUNC('day', timestamp)
    WHEN 'weekly' THEN DATE_TRUNC('week', timestamp)
    ELSE DATE_TRUNC('month', timestamp)
  END AS period
FROM events

The following is an example of using a checkbox to switch which table is referenced.

SELECT * FROM (
  SELECT sensitive_column AS data FROM sensitive_data WHERE {{is_admin}} = TRUE
  UNION ALL
  SELECT public_column AS data FROM filtered_data WHERE {{is_admin}} = FALSE
) t

Selecting multiple values

Text multi input and text multi select expand as a comma-separated string. Use them together with IN.

SELECT *
FROM sales
WHERE metric_name IN ({{selected_metrics}})

The following aggregates only the selected metrics.

SELECT
  SUM(CASE WHEN 'revenue' IN ({{selected_metrics}}) THEN revenue ELSE NULL END) AS revenue_total,
  SUM(CASE WHEN 'cost' IN ({{selected_metrics}}) THEN cost ELSE NULL END) AS cost_total,
  SUM(CASE WHEN 'profit' IN ({{selected_metrics}}) THEN profit ELSE NULL END) AS profit_total
FROM sales

Dynamic identifiers

Placing a parameter inside quotes lets you dynamically generate identifiers such as table or column names. See Escaping and dynamic references for how to write these and points to watch for.