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.
name | date | score |
---|---|---|
John | 2012-01-01 | 3 |
John | 2012-01-01 | 2 |
John | 2012-01-02 | 4 |
Bill | 2012-01-01 | 2 |
Bill | 2012-01-02 | 3 |
Mila | 2012-01-01 | 2 |
Mila | 2012-01-02 | 0 |
Brian | 2012-01-01 | 2.5 |
Brian | 2012-01-02 | 2 |
And we need to output data from this table in the following form:
name | 2012-01-01 | 2012-01-02 |
---|---|---|
Bill | 2 | 3 |
Brian | 2.5 | 2 |
John | 5 | 4 |
Mila | 2 | 0 |
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"]) }};