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.

Note: The Template Engine examples in this article are based on the syntax of the Jinja2/Jinja template language. Actual tools may use similar but different syntax or proprietary extensions.

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
{% raw %}
{% 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 %}
{% endraw %}

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
{% raw %}
{% if start_date %}
  AND order_date >= {{start_date}}
{% endif %}
{% if status %}
  AND status = {{status}}
{% endif %}
{% endraw %}

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
{% raw %}
{% for metric in metrics %}
  SUM({{metric}}) as {{metric}}_total,
{% endfor %}
{% endraw %}
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

UDFs are database-specific features, and notation and capabilities differ between databases.

Template Engine Approach (not in Codatum)

For reference, this would be implemented in a template engine (Jinja2) as:

{% raw %}
{% 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 %}
{% endraw %}

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:

1. SQL injection protection through proper escaping

2. Maintainable SQL structure

3. Better query analysis and optimization

4. Type safety for parameters

5. 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