# Dynamic SQL Charts

## Overview

There are some situations where it is convenient to be able to dynamically build SQL based off of runtime information.

Dynamic SQL is achieved with an embedded scripting language called Handlebars that is interpreted before the SQL is executed by ChartSQL Studio.

```sql
-- @title: Select List - Dynamic SQL query from user selection
-- @subtitle: An example SQL query which uses a dynaic SQL query from user selection
-- @select-list-channel: all, referral, coldcall, search, event
-- @chart: line
-- @formats: currency
SELECT
TRUNC(date_closed, 'MONTH') as Month,
sum(amount) as Sales
FROM sales
{{#unless (eq select-list-channel.selected "all")}}
  WHERE Channel = '{{select-list-channel.selected}}'
{{/unless}}
GROUP BY Month
ORDER BY Month ASC;
```

### Data Context

The data variables that are available for use within the Handlebars script are provided by ChartSQL's various features.

* User Select Lists

## Handlebars Overview

Handlebars is a lightweight templating language embedded within ChartSQL Studio that allows you to dynamically build SQL queries based on runtime data. This flexibility enables customizable and responsive SQL generation tailored to user input and other conditions.

### Variable Output

To display the value of a variable, simply wrap the variable name in double curly braces. This is useful for injecting dynamic data directly into your SQL query.

**Example:**

```sql
SELECT
product_name,
price
FROM
products
WHERE
category = '{{select-list-category.selected}}';
```

In this example, '{{select-list-category.selected}}\` outputs the value of the variable \`select-list-category.selected\`, allowing the user to filter products by category interactively.

### Control Flow

Handlebars provides control structures such as \`#if\`, \`#unless\`, \`#each\`, and \`#with\` to manage the logic within your templates. In SQL, these structures help conditionally include or exclude query parts.

**Example:**

```sql
SELECT
TRUNC(date_closed, 'MONTH') as Month,
sum(amount) as Sales
FROM
sales
{{#unless (eq select-list-channel.selected "all")}}
WHERE
Channel = '{{select-list-channel.selected}}'
{{/unless}}
GROUP BY
Month
ORDER BY
Month ASC;
```

**Explanation:**

*  `{{#unless}}`: This is the inverse of `{{#if}}`. In the provided example, if the selected channel is not "all", the SQL \`WHERE\` clause filters results by the specified channel.

These examples offer a starting point for using Handlebars in your SQL queries. For more advanced usage, refer to the [Handlebars documentation](https://handlebarsjs.com/guide/) for additional functionalities and helpers.

## User Select Lists

Chart SQL queries can be dynamically altered based on a user accessible 'Select List', provided by the [@select-list directive](/reference/directives/select-list.md).

With @select-list, you can alter the SQL query to dynamically choose different columns, table, WHERE conditions in order to create highly customizable charts.

<figure><img src="/files/BGF0cXYi1yV0dj1jp4nK" alt=""><figcaption><p>An example of a chart with a Select List opion.</p></figcaption></figure>

See [@select-list directive](/reference/directives/select-list.md) for details


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.chartsql.com/chartsql-studio/creating-charts/dynamic-sql-charts.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.