Dynamically Generating SQL

In this tutorial we will demonstrate how CoginitiScript could be used to dynamically build SQL queries.

CoginitiScript have a builtin code preprocessor (similar to template engine), which runs on the code before it gets passed to the execution. This preprocessor supports interpolated expressions and control flow statements like conditionals and loops.

Suppose we have a scores table which contains information about players and scores they got on each 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_date;

And we need to output data from this table in the following form:

To do this we have to write a query to pivot data which in general form could be done in the following way:

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
;

As you can see we have to hardcode every date in the SELECT statement for which we are going to produce result. If you want to add another date, you have to copy the column expression.

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",
  SUM(CASE WHEN s.score_date = '2012-01-03' THEN s.score END) AS "2012-01-03" -- we added one more day here
FROM
  scores s
GROUP BY
  name
ORDER BY
  name
;

With CoginitiScript, we could simplify it and build a query dynamically, by creating a block which accepts list of dates. Then we will use #+for loop to iterate over input dates and generate a correspondent column expressions:

#+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

Now we can get a report by simple calling our scoreData block and passing list of dates for which we want to get data. As result our block provides a simple and general API for getting a report for any number of days.

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

Generating score report using date range from the table

Let’s modify our example and instead of passing list of dates as literals into our scoreData block let’s read them from the table / block. To achieve this we need to do following:

• make scoreData to accept a block rather then list of values
• use iterator builtin function to iterate over block results and use obtained date to dynamically build column expression
• define GetDates block to read date range from the table
• pass GetDates as argument into scoreData

#+src sql scoreData(dates)
#+begin
  SELECT
    name,
  #+for d : iterator(dates()) 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'
#+end

SELECT * FROM {{ scoreData(dates=GetDates) }};