Iterator

Iterator is a built-in function which allow users to iterate over block results and perform certain actions. This is especially useful in scenarios where you need to generate SQL dynamically based on the data you get from the query.

iterator function takes a call to the block as parameter and return a list of rows from the block recordset. You can then iterate over recordset using for loop. Use index expressions ["column name"] to reference individual columns from the row.

In the following example, we defined GetDates block which selects dates from the table and then use this data to dynamically generate condition for our SQL query.

Example:

SELECT
  *
FROM
  fact_sales
WHERE
  transaction_date IN(
    #+for row : iterator(GetDates()) separator ", " do
      {{ row["report_date"] }}
    #+end
  )
;

#+src sql GetDates()
#+begin
  SELECT report_date FROM dates_to_be_used_in_report
#+end

Once executed, CoginitiScript generate following query and pass it to target platform for execution (we assume GetDates returns 2012-01-01, 2012-01-02 and 2012-01-03).

SELECT
  *
FROM
  fact_sales
WHERE
  transaction_date IN(
    '2012-01-01', '2012-01-02', '2012-01-03'
  )
;

Please note that code above is just an example of how iterator could be used to solve this specific task. In real scenario to solve this problem is more efficient to use subquery:

SELECT
  *
FROM
  fact_sales
WHERE
  transaction_date IN(
    SELECT report_date FROM {{ GetDates() }}
  )
;