Skip to main content

CoginitiScript: Generating SQL Dynamically

CoginitiScript includes a powerful preprocessor that acts like a template engine, allowing you to generate SQL dynamically using interpolated expressions, loops, and conditionals. This guide shows you how to build flexible, reusable SQL queries that adapt to different data requirements.

What You'll Learn​

  • How to use CoginitiScript's preprocessor for dynamic SQL
  • How to create pivot tables with dynamic columns
  • How to use loops and conditionals in SQL generation
  • How to work with data-driven query construction
  • Best practices for maintainable dynamic queries

Prerequisites​

  • Basic understanding of CoginitiScript syntax
  • Familiarity with SQL and pivot operations
  • Understanding of template concepts

The Problem: Static SQL Limitations​

Consider a common scenario where you need to create pivot tables. You have a scores table with player scores by date:

CREATE TABLE scores AS
SELECT 'John' AS name, '2012-01-01' AS score_date, 3 AS score UNION ALL
SELECT 'John' AS name, '2012-01-01' AS score_date, 2 AS score UNION ALL
SELECT 'John' AS name, '2012-01-02' AS score_date, 4 AS score UNION ALL
SELECT 'Bill' AS name, '2012-01-01' AS score_date, 2 AS score UNION ALL
SELECT 'Bill' AS name, '2012-01-02' AS score_date, 3 AS score UNION ALL
SELECT 'Mila' AS name, '2012-01-01' AS score_date, 2 AS score UNION ALL
SELECT 'Mila' AS name, '2012-01-02' AS score_date, 0 AS score UNION ALL
SELECT 'Brian' AS name, '2012-01-01' AS score_date, 2.5 AS score UNION ALL
SELECT 'Brian' AS name, '2012-01-02' AS score_date, 2 AS score;

Sample Data:

namescore_datescore
John2012-01-013
John2012-01-012
John2012-01-024
Bill2012-01-012
Bill2012-01-023
Mila2012-01-012
Mila2012-01-020
Brian2012-01-012.5
Brian2012-01-022

You want to pivot this data to show scores by date:

Desired Output:

name2012-01-012012-01-02
Bill23
Brian2.52
John54
Mila20

Traditional Static Approach​

The conventional SQL approach requires hardcoding each date:

SELECT
  name,
  SUM(CASE WHEN s.score_date = '2012-01-01' THEN s.score END) AS "2012-01-01",
  SUM(CASE WHEN s.score_date = '2012-01-02' THEN s.score END) AS "2012-01-02"
FROM
  scores s
GROUP BY
  name
ORDER BY
  name;

Problems with this approach:

  • Manual column creation: Each date requires a separate CASE expression
  • Code duplication: Similar patterns repeated for each column
  • Maintenance burden: Adding new dates requires modifying the query
  • Inflexibility: Cannot easily adapt to different date ranges

The Solution: Dynamic SQL Generation​

Step 1: Create a Dynamic Pivot Block​

Use CoginitiScript's preprocessor to generate columns dynamically:

#+src sql scoreData(dates)
#+begin
  SELECT
    name,
  #+for d : dates separator "," do
    SUM(CASE WHEN s.score_date = '{{"{{ d }}"}}' THEN s.score END) AS "{{"{{ d }}"}}"
  #+end
  FROM
    scores s
  GROUP BY
    name
  ORDER BY
    name;
#+end

Key elements:

  • #+for d : dates: Loops through the dates array
  • separator ",": Adds commas between generated columns
  • {{"{{ d }}"}}: Interpolates each date value
  • Template generates one CASE expression per date

Step 2: Call the Dynamic Block​

Use the block with a list of dates:

SELECT * FROM {{"{{ scoreData(dates=[\"2012-01-01\", \"2012-01-02\"]) }}"}};

Step 3: See the Generated SQL​

CoginitiScript expands the template to produce:

SELECT
  name,
  SUM(CASE WHEN s.score_date = '2012-01-01' THEN s.score END) AS "2012-01-01",
  SUM(CASE WHEN s.score_date = '2012-01-02' THEN s.score END) AS "2012-01-02"
FROM
  scores s
GROUP BY
  name
ORDER BY
  name;

Advanced Technique: Data-Driven Generation​

Using the Iterator Function​

Instead of hardcoding dates, generate them from data using the iterator() function:

#+src sql scoreDataFromQuery(dateSource)
#+begin
  SELECT
    name,
  #+for d : iterator(dateSource()) separator "," do
    SUM(CASE WHEN s.score_date = '{{"{{ d[\"report_date\"] }}"}}' THEN s.score END) AS "{{"{{ d[\"report_date\"] }}"}}"
  #+end
  FROM
    scores s
  GROUP BY
    name
  ORDER BY
    name;
#+end

#+src sql GetDates()
#+begin
  SELECT '2012-01-01' AS report_date 
  UNION ALL 
  SELECT '2012-01-02' AS report_date;
#+end

Call with Dynamic Date Source​

SELECT * FROM {{"{{ scoreDataFromQuery(dateSource=GetDates) }}"}};

How it works:

  1. GetDates() returns a recordset with date values
  2. iterator(dateSource()) converts the recordset to an iterable collection
  3. Loop processes each row, accessing the report_date column
  4. Dynamic columns are generated based on actual data

More Dynamic SQL Patterns​

Conditional Column Generation​

Generate columns based on conditions:

#+src sql salesReport(includeQuarterly, includeYearly)
#+begin
  SELECT
    product_name,
    SUM(revenue) as total_revenue,
  #+if includeQuarterly then
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 1 THEN revenue END) as q1_revenue,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 2 THEN revenue END) as q2_revenue,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 3 THEN revenue END) as q3_revenue,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 4 THEN revenue END) as q4_revenue,
  #+end
  #+if includeYearly then
    SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) = 2023 THEN revenue END) as revenue_2023,
    SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) = 2024 THEN revenue END) as revenue_2024
  #+end
  FROM sales
  GROUP BY product_name;
#+end

Usage:

-- Include both quarterly and yearly breakdowns
SELECT * FROM {{"{{ salesReport(includeQuarterly=true, includeYearly=true) }}"}};

-- Only quarterly breakdown
SELECT * FROM {{"{{ salesReport(includeQuarterly=true, includeYearly=false) }}"}};

Dynamic WHERE Clauses​

Build flexible filtering conditions:

#+src sql customerAnalysis(regions, minRevenue, includeInactive)
#+begin
  SELECT
    customer_id,
    name,
    region,
    total_revenue,
    status
  FROM customers
  WHERE 1=1
  #+if regions != null && length(regions) > 0 then
    AND region IN (
    #+for r : regions separator "," do
      '{{"{{ r }}"}}'
    #+end
    )
  #+end
  #+if minRevenue != null then
    AND total_revenue >= {{"{{ minRevenue }}"}}
  #+end
  #+if !includeInactive then
    AND status = 'active'
  #+end
  ORDER BY total_revenue DESC;
#+end

Usage:

-- All active customers in specific regions with minimum revenue
SELECT * FROM {{"{{ customerAnalysis(
  regions=[\"North\", \"South\"], 
  minRevenue=10000, 
  includeInactive=false
) }}"}};

Dynamic Aggregation Levels​

Create reports with different grouping levels:

#+src sql salesSummary(groupBy, metrics)
#+begin
  SELECT
  #+for field : groupBy separator "," do
    {{"{{ field }}"}}
  #+end,
  #+for metric : metrics separator "," do
    #+if metric == "revenue" then
      SUM(revenue) as total_revenue
    #+else if metric == "quantity" then
      SUM(quantity) as total_quantity
    #+else if metric == "orders" then
      COUNT(DISTINCT order_id) as total_orders
    #+else if metric == "avg_order_value" then
      AVG(revenue) as avg_order_value
    #+end
  #+end
  FROM sales
  #+if length(groupBy) > 0 then
    GROUP BY
    #+for field : groupBy separator "," do
      {{"{{ field }}"}}
    #+end
  #+end;
#+end

Usage:

-- Group by region and product, show revenue and order count
SELECT * FROM {{"{{ salesSummary(
  groupBy=[\"region\", \"product_category\"],
  metrics=[\"revenue\", \"orders\"]
) }}"}};

Best Practices​

1. Use Constants for Reusability​

Define commonly used values as constants:

#+const
  standardDateColumns = ["created_date", "updated_date", "deleted_date"];
  commonMetrics = ["count", "sum", "avg", "min", "max"];
#+end

#+src sql auditReport(tables, dateColumns, metrics)
#+begin
  -- Use constants when no specific values are provided
  #+const actualDateColumns = dateColumns != null ? dateColumns : standardDateColumns;
  #+const actualMetrics = metrics != null ? metrics : commonMetrics;
  
  -- Generate report using the resolved constants
  SELECT table_name,
  #+for metric : actualMetrics separator "," do
    {{"{{ metric }}"}}(record_count) as {{"{{ metric }}"}}_records
  #+end
  FROM table_stats;
#+end

2. Validate Parameters​

Add parameter validation to prevent errors:

#+src sql pivotData(dimensions, measures, filters)
#+begin
  #+if dimensions == null || length(dimensions) == 0 then
    -- Error: At least one dimension is required
    SELECT 'Error: No dimensions specified' as error_message;
  #+else if measures == null || length(measures) == 0 then
    -- Error: At least one measure is required  
    SELECT 'Error: No measures specified' as error_message;
  #+else
    -- Generate the actual pivot query
    SELECT
    #+for dim : dimensions separator "," do
      {{"{{ dim }}"}}
    #+end,
    #+for measure : measures separator "," do
      SUM({{"{{ measure }}"}}) as total_{{"{{ measure }}"}}
    #+end
    FROM fact_table
    WHERE 1=1
    #+if filters != null then
      #+for filter : filters do
        AND {{"{{ filter }}"}}
      #+end
    #+end
    GROUP BY
    #+for dim : dimensions separator "," do
      {{"{{ dim }}"}}
    #+end;
  #+end
#+end

3. Document Template Logic​

Add clear documentation for complex templates:

#+src sql dynamicCrossTab(rowDimension, colDimension, measure, aggregation)
#+meta {
  :doc "Creates a dynamic cross-tabulation report.
  
  Parameters:
  - rowDimension: Column name for row grouping
  - colDimension: Column name that becomes pivot columns  
  - measure: Numeric column to aggregate
  - aggregation: Type of aggregation (SUM, AVG, COUNT, etc.)
  
  Example:
  {{ dynamicCrossTab(
    rowDimension=\"product_category\",
    colDimension=\"region\", 
    measure=\"revenue\",
    aggregation=\"SUM\"
  ) }}
  
  Generates pivot table with product categories as rows,
  regions as columns, and revenue sums as values."
}
#+begin
  -- Implementation here
#+end

4. Test Template Expansion​

Always test your templates with different parameter combinations:

-- Test with minimal parameters
SELECT * FROM {{"{{ scoreData(dates=[\"2012-01-01\"]) }}"}};

-- Test with multiple parameters  
SELECT * FROM {{"{{ scoreData(dates=[\"2012-01-01\", \"2012-01-02\", \"2012-01-03\"]) }}"}};

-- Test edge cases
SELECT * FROM {{"{{ scoreData(dates=[]) }}"}}; -- Empty array

5. Use Meaningful Variable Names​

Choose descriptive names for loop variables and parameters:

-- Good: Clear variable names
#+for dateValue : reportDates separator "," do
  SUM(CASE WHEN order_date = '{{"{{ dateValue }}"}}' THEN amount END) as "{{"{{ dateValue }}"}}"
#+end

-- Avoid: Unclear variable names
#+for d : dates separator "," do
  SUM(CASE WHEN order_date = '{{"{{ d }}"}}' THEN amount END) as "{{"{{ d }}"}}"
#+end

Advanced Patterns​

Nested Loops for Complex Structures​

Generate multi-level pivot tables:

#+src sql multiLevelPivot(rowDimensions, colDimensions, measure)
#+begin
  SELECT
  #+for rowDim : rowDimensions separator "," do
    {{"{{ rowDim }}"}}
  #+end,
  #+for colDim : colDimensions do
    #+for colValue : iterator(getDistinctValues(colDim)) separator "," do
      SUM(CASE WHEN {{"{{ colDim }}"}} = '{{"{{ colValue[\"value\"] }}"}}' 
               THEN {{"{{ measure }}"}} END) as "{{"{{ colDim }}"}}__{{"{{ colValue[\"value\"] }}"}}"
    #+end
  #+end
  FROM fact_table
  GROUP BY
  #+for rowDim : rowDimensions separator "," do
    {{"{{ rowDim }}"}}
  #+end;
#+end

Conditional Template Logic​

Build templates that adapt to different database platforms:

#+src sql platformSpecificQuery(platform, tableName, columns)
#+begin
  SELECT
  #+for col : columns separator "," do
    #+if platform == "snowflake" then
      UPPER({{"{{ col }}"}}) as {{"{{ col }}"}}_upper
    #+else if platform == "bigquery" then
      LOWER({{"{{ col }}"}}) as {{"{{ col }}"}}_lower  
    #+else
      {{"{{ col }}"}}
    #+end
  #+end
  FROM {{"{{ tableName }}"}}
  #+if platform == "snowflake" then
    LIMIT 1000
  #+else if platform == "bigquery" then  
    LIMIT 500
  #+end;
#+end

Troubleshooting​

Common Issues and Solutions​

Issue: Loop generates invalid SQL syntax

-- Problem: Missing separator creates invalid SQL
#+for col : columns do
  {{"{{ col }}"}}
#+end

-- Solution: Add separator
#+for col : columns separator "," do
  {{"{{ col }}"}}
#+end

Issue: Template produces empty results

-- Problem: Empty array or null parameters
#+for item : emptyArray do
  {{"{{ item }}"}}
#+end

-- Solution: Add conditional check
#+if items != null && length(items) > 0 then
  #+for item : items separator "," do
    {{"{{ item }}"}}
  #+end
#+else
  1 as placeholder_column
#+end

Issue: Complex expressions break template parsing

-- Problem: Complex interpolation
{{"{{ someFunction(param1, param2).property }}"}}

-- Solution: Use intermediate variables
#+const result = someFunction(param1, param2);
{{"{{ result.property }}"}}

Debugging Tips​

  1. Start simple: Begin with basic templates and add complexity gradually
  2. Test expansion: Use simple queries to verify template expansion
  3. Check syntax: Ensure generated SQL is syntactically correct
  4. Use logging: Add comments to track template execution
  5. Validate parameters: Check for null or empty values

Next Steps​

Summary​

Dynamic SQL generation with CoginitiScript enables you to:

  • Create flexible, parameterized queries
  • Eliminate code duplication in pivot operations
  • Build data-driven report structures
  • Adapt queries to different requirements automatically

By leveraging CoginitiScript's preprocessor features—loops, conditionals, and interpolation—you can create powerful, maintainable SQL generation patterns that scale with your data needs.