Query Chaining
Query chaining enables analysts to reference the results of a query from other queries.
Syntax
Reference other queries by writing the query name inside ${ }
.
For example, if you want to reference a query named daily_sales
, you would write ${daily_sales}
into your SQL query.
Example
Here we have a normal SQL query, called sales_by_region:
select
region,
sum(sales) as sales
from production.daily_sales
group by 1
Next, we have a query calculating average sales across regions, which references sales_by_region using the ${ }
syntax.
select
avg(sales) as average_sales
from ${sales_by_region}
Below is the compiled SQL that's sent to the database:
select
avg(sales) as average_sales
from (
select
region,
sum(sales) as sales
from production.daily_sales
group by 1
)
Presentation
You can choose whether you want to see the compiled or written SQL inside the query viewer:
Other
The order that queries appear on the page doesn't matter to the SQL compiler. You can reference queries that appear before or after the query that you are authoring.
Some SQL dialects require sub-queries to be aliased, including Postgres and MySQL. E.g. from ${sales_by_region} as sales_by_region
.
The SQL compiler detects circular and missing references. If a query includes either a circular reference or a missing reference, Evidence will display an error that looks like a syntax error in a normal SQL query. Queries with compiler errors are not sent to your database.