Using Macros

In this tutorial we will talk about macros and show how they helps with code reuse.

A macro is a named block with a piece of code in it. When the macro gets called from the interpolation expressions the content of the macro block is replaced before the program execution. Macros are useful when you want to have to reuse part of the SQL statement.

Suppose you have a CASE expression which you use when you need to get a country group using country code. It might look something 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
;

There are several places where you have to use it to calculate country group. Most likely you just copy paste it and that’s it. The immediate problem arise is when you have to change logic for this expressions, since now you have to modify it in all places where it's being used.

With CoginitiScript you can create a macro for this expression and reference it from queries. We also want to pass a countryField argument with a name of the column where country code is stored, so this macro could be used in many different contexts.

#+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

To use given macro just call it from interpolation expression as you call other blocks.

SELECT
  {{ countryGroup(country="country") }} AS country_group,
  sum(revenue) AS revenue
FROM
  fact_sales
GROUP BY
  country_group
;

CoginitiScript will expand countryGroup call into the content of the macro body. Our final SQL before it will be passed for execution will be:

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
GROUP BY
  country_group
;

If later on you need to modify logic of this expression you do it only in one place (macro definition).

Please note that CoginitiScript doesn’t know any context in which macro content is substituted and doesn’t perform any validation that after expanding of macro you will get a valid SQL script. It’s up to the user to make sure that macro has been called in the correct context.