CoginitiScript Macros
Introduction to CoginitiScript
In SQL, macros are pre-written SQL codes that can be called and executed repeatedly to simplify complex queries. CoginitiScript Macro has the same idea, its is a reusable piece of code. When you call a macro in a query, its content is substituted inline prior to query execution.
They are handy for simplifying repetitive or complex queries, reducing the amount of code that needs to be written, and making it easier to maintain and update queries over time. By defining macros, you can create reusable code that can be used throughout your codebase, making it easier to develop and manage complex queries.
Suppose you have a CASE expression to get a country group using country code. It might look like this:
SELECT
CASE
WHEN country IN ('US', 'CA') THEN 'NA'
WHEN country IN ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') THEN 'EU'
WHEN country IN ('AU') THEN country
ELSE 'Other'
END AS country_group,
....
FROM fact_sales;
GROUP BY country_group;
Instead of copy-pasting it whenever you need it, you can create a macro for this expression and reference it. Using a macro will avoid changing the logic for these expressions in all places where they are being used, for example.
How to create a macro
Macros are defined using an #+macro command.
#+macro countryGroup(countryField)
#+meta {
:doc "Given a name of the country column returns a CASE statement to get a country group."
}
#+begin
CASE
WHEN {{ countryField }} IN ('US', 'CA') THEN 'NA'
WHEN {{ countryField }} IN ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') THEN 'EU'
WHEN {{ countryField }} IN ('AU') THEN {{ countryField }}
ELSE 'Other'
END
#+end
This code defines a macro named countryGroup, which accepts a parameter countryField that represents the name of a column in a SQL table that contains country values. The macro returns a CASE statement that will group countries into different categories.
You can use the “#+meta” command to add metadata and the “:doc” parameter for documentation.
Here’s an example of using the macro above:
SELECT
{{ countryGroup(country="country") }} AS country_group,
sum(revenue) AS revenue
FROM fact_sales
<span class="sql-language"><a href="/tutorials/intermediate/sql-group-by/">GROUP BY</a></span> country_group;
Note that CoginitiScript will expand countryGroup into the content of the macro body, and, before the execution, the SQL will be like the example below.
SELECT
CASE
WHEN country IN ('US', 'CA') THEN 'NA'
WHEN country IN ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') THEN 'EU'
WHEN country IN ('AU') THEN country
ELSE 'Other'
END AS country_group,
sum(revenue) AS revenue
FROM fact_sales
<span class="sql-language"><a href="/tutorials/intermediate/sql-group-by/">GROUP BY</a></span> country_group;
Later on, to modify the logic of this expression, you’ll only need to update the macro.