SQL variables

Add interactive parameters to your SQL queries that can be tested in insights and used as filters in reports.

Think of blanks in a mail merge letter - you write the letter once with placeholders for "Name" and "Department", then each time you use it, you can insert different values. SQL variables let you write a query that shows different results based on the values you provide.

What SQL variables are

SQL variables are placeholders in your SQL queries that allow you to:

Create flexible queries - Write once, use with different values
Test different scenarios - Try different values without rewriting SQL
Build dynamic reports - Same query shows different data based on user selection
Connect with report filters - Variables become user-friendly filters in reports

Real-world example: Instead of writing separate queries for each department, write one query with $department and then test it with "Public Safety", "Health Services", or any other department name.

Variable syntax

Variables use simple dollar sign syntax in your SQL queries.

SELECT department, status, budget, created_date
FROM projects 
WHERE department = $department_filter
  AND status = $project_status
  AND budget >= $minimum_budget
  AND created_date >= $start_date

Note: Currently, variables can only be used in WHERE clauses.

Rules for variable names:

Must start with letter or underscore: $department ✅, $_filter ✅
Can contain letters, numbers, underscores: $region_2024 ✅, $status_filter ✅
Cannot start with numbers: $2024_data ❌
Cannot contain spaces or special characters: $my-filter ❌, $my filter ❌

Current limitations:

WHERE clause only: Variables can only be used in WHERE conditions
No column names: Variables cannot be used for SELECT columns like SELECT $column_name FROM table
No GROUP BY/ORDER BY: Variables cannot be used in GROUP BY $group_field or ORDER BY $sort_field

For complete technical details see DuckDB's documentation on named parameters.

Use descriptive names like $department_filter or $start_date instead of generic names like $var1 or $x. This makes your queries easier to understand later.

How variables work in the interface

When you write a SQL query with variables, the system automatically detects them and creates interactive controls above the table preview or chart preview on the left side of the interface.

Variable controls

Each variable gets its own small control where you have the following options:

Test with different variable types

You can test your variables with different data types:

Text - For names, categories, descriptions
Number - For amounts, counts, IDs
Date - For time-based filtering (YYYY-MM-DD format)

The interface lets you test how your query behaves with different kinds of values.

Choose single or multiple values

Single value mode:

Enter one value that replaces the variable
Example: $department becomes "Public Safety"

Multiple values mode:

Enter multiple values separated by semicolons
Example: $status_list becomes "Active; Pending; Review"
Use with IN clause: WHERE status IN ($status_list)

Enter your test values

For text values:

Type directly: Public Safety
Multiple values: Active; Pending; Completed
Include literal semicolons: Text with \; semicolon

For numbers:

Single: 1000
Multiple: 100; 500; 1000

For dates:

Always use YYYY-MM-DD format: 2024-01-15
Single date only (no multiple date values)

Test your query

Click "Apply" on the variable pill to test with those values, then run your query to see the results.

Common variable patterns

Single value filtering

SELECT department, COUNT(*) as employee_count
FROM employees 
WHERE department = $department
GROUP BY department
ORDER BY employee_count DESC

Variable setup:

Name: department
Test with: Text values like Public Safety
Mode: Single value

Multiple value filtering

SELECT * 
FROM projects
WHERE status IN $status_list
  AND priority IN $priority_list
ORDER BY created_date DESC

Variable setup:

Name: status_list
Test with: Multiple text values like Active; Review; Planning
Mode: Multiple values

Date range filtering

SELECT * 
FROM budget_items
WHERE created_date >= $start_date 
  AND created_date <= $end_date
  AND amount >= $minimum_amount
ORDER BY created_date

Variable setup:

start_date: Test with date values like 2024-01-01
end_date: Test with date values like 2024-12-31
minimum_amount: Test with number values like 1000

Handling empty variables

When you don't want a filter to apply, use this pattern to show all data:

SELECT * 
FROM projects
WHERE (department = $department OR $department IS NULL)
  AND (status IN $status_list OR $status_list IS NULL)

This ensures:

If you set $department to "Public Safety", show only those projects
If you leave $department empty, show ALL departments
Same logic works for the status list

Pro tip: Always include the "OR $variable IS NULL" pattern for optional filters. This makes your queries work whether filters are set or not, which is especially important when using variables in reports.

Testing variables in insights

Variables are perfect for testing different scenarios when building insights:

Development workflow

Write your base query

Start with a query that works without variables:

SELECT department, AVG(budget) as average_budget
FROM projects 
WHERE status = 'Active'
GROUP BY department

Add variables for flexibility

Replace fixed values with variables:

SELECT department, AVG(budget) as average_budget
FROM projects 
WHERE (status IN $status_filter OR $status_filter IS NULL)
  AND (department = $department_filter OR $department_filter IS NULL)
GROUP BY department

Test different scenarios

Use the variable pills to test:

All active projects: status_filter = Active
Active and pending: status_filter = Active; Pending
Specific department: department_filter = Health Services
All departments: leave department_filter empty

Verify results make sense

Check that your query returns expected results for each test case before using it in reports.

From variables in insights to report filters

The real power of variables shows when you use insights with variables in reports:

The connection

In insights: Variables let you test different query parameters
In reports: Those same variables can become user-friendly filters
For report end users: They see dropdowns and date pickers, not raw SQL code

Example transformation

Your SQL insight with variables:

SELECT department, COUNT(*) as project_count
FROM projects
WHERE (status IN $project_status OR $project_status IS NULL)
  AND (created_date >= $start_date OR $start_date IS NULL)

Becomes this for report users:

Project Status filter with choices: "Active", "Pending", "Completed"
Start Date filter for selecting the earliest date to include

Why this matters

Developers work with flexible SQL queries using variables
Report builders connect variables to user-friendly filter interfaces
End users get intuitive controls without seeing or editing SQL code

For complete details on connecting variables to report filters see Report Filters.

Tips

Variable names

Be descriptive: $department_filter not $d
Be consistent: If you use the _filter suffix, use it everywhere
Match your data: Use names that align with your column names when possible

Query design

Handle nulls properly: Always include OR $variable IS NULL for optional filters
Test thoroughly: Try empty values, single values, and multiple values
Use appropriate SQL clauses: = for single values, IN for lists

Variable types

Text: For categories, names, descriptions
Number: For amounts, counts, IDs
Date: For date values only, always YYYY-MM-DD format

Testing workflow

Start simple: Test with single values first
Add complexity: Try multiple values and combinations
Test edge cases: Empty values, all values, no matches
Verify logic: Make sure NULL handling works correctly

Remember: Variables are case-sensitive. $Department and $department are different variables. Stick with lowercase and underscores for consistency.

Common troubleshooting

Variable not recognized

Problem: You typed $department but don't see any variable being recognized

Solution: Check spelling and make sure the variable name follows naming rules

Query fails with variables

Problem: Query works without variables but fails when you add them

Solution: Check your NULL handling - add OR $variable IS NULL conditions

Wrong data type

Problem: Your date variable shows text values or numbers are displayed as text

Solution: Test with the correct data type in the variable interface - try date format YYYY-MM-DD for dates

Multiple values not working

Problem: Multiple values in a variable aren't filtering correctly

Solution: Make sure you use IN ($variable) not = $variable for lists

Advanced patterns

Complex filtering

SELECT * 
FROM projects
WHERE 
  -- Date range filtering
  (created_date BETWEEN $start_date AND $end_date 
   OR ($start_date IS NULL AND $end_date IS NULL))
  
  -- Multi-criteria filtering  
  AND (
    (department IN $department_list OR $department_list IS NULL)
    AND (status IN $status_list OR $status_list IS NULL)
    AND (budget >= $minimum_budget OR $minimum_budget IS NULL)
  )

Remember: Variables can only be used in WHERE clauses. Complex logic with CASE statements, dynamic column selection, or variable GROUP BY/ORDER BY clauses are not currently supported.

Ready to use variables in reports? Learn how to connect your SQL insights with user-friendly report filters in the Report Filters documentation.

VorherigeSQL Commands NächsteSQL Editor

Zuletzt aktualisiert vor 1 Tag

War das hilfreich?