Expressions, Loops and Conditions

The source code inside blocks are treated as templates which get preprocessed before passing to the host language interpreter.

Inside templates users are able to use control flow constructs, like loops and conditions and interpolation expressions. Interpolation expressions within templates are evaluated and their string representation is automatically inserted at the position. If an interpolation expression evaluates to null an empty string is added.



Expressions insert value of the CoginitiScript expression into a final code. {{ and }} delimiters are used to insert an expression result into a final SQL code. We call them interpolated expressions because the result of the expression will be substituted in the source code as-is before passing it to the host language in a similar manner as template engines like Jinja2 do.


SELECT {{ 1 + 1 }}    ---> SELECT 2
SELECT {{ 2 > 5 }}    ---> SELECT false
SELECT {{ "string" }} ---> SELECT string


Take into account that string values will be inserted into the final SQL code without quotes. This is done in order to be able to generate dynamic names of identifiers in SQL, like column names, table names, etc.

  columnPrefix = "sales";

SELECT {{ prefix }}_amount FROM fact_sales;

Given code will generate following final SQL:

SELECT sales_amount FROM fact_sales;

If you want string values to be inserted as strings in SQL, you have to wrap your expression in quotes:

paymentType = "credit_card";

SELECT '{{ paymentType }}';

-- Following SQL will be generated:
SELECT 'credit_card';


Conditions allows to execute arbitrary piece of code based on condition. It is defined using #+if statement. Following is a simple example where we just check in expression if debug variable is true and perform certain actions there.

#+if debug == true then
  SELECT ...;

You can also use IF...ELSE...END constructs.

#+if debug == true then
   SELECT ...;
   SELECT ....;



Provides ability to loop over items in the collection.

#+for el : ["one", "two", "three"] do
  {{ el }}

It optionally allows to specify what to put in-between (SEPARATOR) of all iterations. It is executed if there are at least two iterations. One useful scenario for it is to add comma separators for a list of column names in your SQL statement:

#+src sql Customers(fields)
#+for f : fields separator "," do
   {{ el }}

SELECT * FROM {{ Customers(["first_name", "last_name", "email"]) }};


Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request