Advanced Control Structures in SQL
Learn how to implement template engine-like functionality securely using Codatum's parameter features
There are situations where you might want to branch or repeat processes based on parameter input or reuse the same process functionally. In such cases, many SQL tools use template engines like Jinja to generate dynamic SQL queries.
However, in Codatum, security concerns such as SQL Injection are prioritized, and template engines cannot be used. By maintaining syntax parsing, Codatum ensures:
Accurate escaping based on context, reducing security risks
Easier debugging by pinpointing the exact location of errors
Context-based suggestions for improved usability
Most of these cases can be addressed by utilizing standard SQL syntax. This article introduces methods for implementing advanced control structures in Codatum.
Before reading this section, please read the Parameter and Escape and Dynamic Reference sections.
1. Conditional Data Access (If/Else)
Controls which fields or data to SELECT based on parameters.
Use Cases
Apply different business logic by region/country
Change data aggregation granularity dynamically
Restrict data access based on user permission levels
Provide different data views by department/team
Codatum Approach
Use CASE expressions or UNION with parameters:
-- Region-specific business logic example
SELECT
order_id,
CASE {{region}}
WHEN 'EU' THEN amount * 1.20 -- EU: 20% VAT
ELSE amount
END as final_price,
CASE {{region}}
WHEN 'EU' THEN 'EU'
ELSE 'Other'
END as tax_region
FROM orders
-- Aggregation granularity example
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
-- Permission-based restriction example
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
Template Engine Approach (not in Codatum)
For reference, this would be implemented in a template engine (Jinja2) as:
-- Region-specific business logic example
{% if region == 'EU' %}
SELECT
order_id,
amount * 1.20 as price_with_vat, -- EU: 20% VAT
'EU' as tax_region
FROM orders
{% else %}
SELECT
order_id,
amount as price_without_vat,
'Other' as tax_region
FROM orders
{% endif %}
-- Aggregation granularity example
SELECT
{% if granularity == 'daily' %}
DATE_TRUNC('day', timestamp) as period
{% elif granularity == 'weekly' %}
DATE_TRUNC('week', timestamp) as period
{% else %}
DATE_TRUNC('month', timestamp) as period
{% endif %}
FROM events
-- Permission-based restriction example
{% if user.is_admin %}
SELECT * FROM sensitive_data
{% else %}
SELECT public_columns FROM filtered_data
{% endif %}
2. Optional Filter Conditions (If/Else)
Controls which conditions to include in WHERE clause based on parameters.
Use Cases
Allow users to combine search conditions flexibly
Change report filters dynamically
Apply filters only when specific conditions are provided
Codatum Approach
Use empty parameter handling:
SELECT * FROM orders
WHERE 1=1
AND (order_date >= {{start_date}} OR {{start_date}} = '1970-01-01')
AND (status = {{status}} OR {{status}} = '')
Template Engine Approach (not in Codatum)
For reference, this would be implemented in a template engine (Jinja2) as:
SELECT * FROM orders
WHERE 1=1
{% if start_date %}
AND order_date >= {{start_date}}
{% endif %}
{% if status %}
AND status = {{status}}
{% endif %}
3. Repeat by Multi Parameters (for Loop)
Apply repetition patterns using Multi Input/Select parameters.
Use Cases
Aggregate multiple metrics simultaneously
Display only user-selected metrics
Apply similar calculation logic to multiple columns
Codatum Approach
Use Text Multi Input parameter with CASE expressions to control which metrics to aggregate:
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
While this approach requires more code than template engines, it provides better reliability and maintainability:
Each metric is explicitly defined, making it easier to detect errors at compile time
Column names and types can be validated by the SQL engine
Changes to table schema are less likely to break the query
Template Engine Approach (not in Codatum)
For reference, this would be implemented in a template engine (Jinja2) as:
SELECT
{% for metric in metrics %}
SUM({{metric}}) as {{metric}}_total,
{% endfor %}
FROM sales
4. Dynamic Identifier Generation
Controls which table to query based on parameters.
Use Cases
Select tables by API key
Access date-partitioned tables
Generate schema or table names dynamically
Codatum Approach
Use quoted parameters:
-- API key-based table selection
SELECT * FROM `api_logs_{{api_key_suffix}}` -- e.g., api_logs_v1, api_logs_v2
-- Date-partitioned table access
SELECT * FROM `events_{{date_suffix}}` -- e.g., events_20240131
FROM `sales_{{region}}_{{date_suffix}}` -- e.g., sales_us_20240131
-- Dynamic column names
SELECT
"{{metric_name}}" as metric, -- e.g., revenue_usd, revenue_eur
"amount_{{currency}}" as amount -- e.g., amount_usd, amount_eur
FROM transactions
Note: In BigQuery connections, date parameters in table names are automatically formatted as YYYYMMDD
.
Template Engine Approach (not in Codatum)
For reference, this would be implemented in a template engine (Jinja2) as:
-- API key-based table selection
SELECT * FROM `api_logs_{{api_version}}`
-- Date-partitioned table access
SELECT * FROM `events_{{date.strftime('%Y%m%d')}}`
-- Dynamic column names
SELECT
{{metric_name}} as metric,
amount_{{currency}} as amount
FROM transactions
5. Macro/Function Reuse
Encapsulates reusable calculation logic into functions.
Use Cases
Standardize domain-specific calculation logic
Reuse calculation logic across multiple queries
Encapsulate complex conditional calculations
Codatum Approach (Using BigQuery UDF)
-- LTV calculation function
CREATE TEMP FUNCTION calculate_ltv(revenue FLOAT64, acquisition_cost FLOAT64, retention_months INT64)
RETURNS FLOAT64
AS (
CASE
WHEN retention_months = 0 THEN 0.0
ELSE (revenue - acquisition_cost) * POWER(0.95, retention_months)
END
);
SELECT
user_id,
calculate_ltv(total_revenue, acquisition_cost, months_active) as ltv,
FROM user_metrics
Template Engine Approach (not in Codatum)
For reference, this would be implemented in a template engine (Jinja2) as:
{% macro calculate_ltv(revenue, acquisition_cost, retention_months) %}
CASE
WHEN {{retention_months}} = 0 THEN 0
ELSE ({{revenue}} - {{acquisition_cost}}) *
POWER(0.95, {{retention_months}}) -- Apply 5% decay rate
END
{% endmacro %}
SELECT
user_id,
{{ calculate_ltv('total_revenue', 'acquisition_cost', 'months_active') }} as ltv
FROM user_metrics
Benefits of Codatum's Approach
While template engines offer more flexibility, Codatum's parameter-based approach provides:
SQL injection protection through proper escaping
Maintainable SQL structure
Better query analysis and optimization
Type safety for parameters
Clear separation between dynamic values and SQL structure
When template engine functionality is required, consider:
Restructuring queries to use standard SQL features
Breaking complex queries into multiple simpler queries
Using CTEs for query organization
Leveraging database-specific features when available
Last updated
Was this helpful?