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 following table which contains information about players and scores they got on each 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 DATE_CMP(s.score_date, '2012-01-01') = 0 THEN s.score END) AS "2012-01-01",
  SUM(CASE WHEN DATE_CMP(s.score_date, '2012-01-02') = 0 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 DATE_CMP(s.score_date, '2012-01-01') = 0 THEN s.score END) AS "2012-01-01",
  SUM(CASE WHEN DATE_CMP(s.score_date, '2012-01-02') = 0 THEN s.score END) AS "2012-01-02"
  SUM(CASE WHEN DATE_CMP(s.score_date, '2012-01-03') = 0 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 DATE_CMP(s.score_date, '{{ d }}') = 0 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"]) }};