Use Jinja and SQL to Build a Macro

You’ll learn how to build a simple macro and a macro that uses another macro.

What Is a Macro?

A macro is a reusable block of code that you can define once and use multiple times. Macros can help keep your code DRY(Don’t Repeat Yourself).

Macro Syntax

Macros are defined by using the keyword macro. Notice in the example, {{ flower_shop(type = 'white') }}, only type was supplied, not flower. It used the default flower = ‘rose’ in the macro definition.

{%- macro flower_shop(type, flower = 'rose') -%}

Today I want a {{ type }} {{ flower }}.

{%- endmacro -%}

{{ flower_shop(type = 'white') }} //Today I want a white rose. 

{{ flower_shop('purple', 'orchid') }}// Today I want a purple orchid.

Macro Uppercase Example

You can use this macro to change all the column data to UPPERCASE.

1. To run this macro, go to Build Settings > Macros.
2. Copy and paste the macro there.
3. In your Node Editor, either double click on the Transforms field or select the column and open the Column Editor.
4. Enter {{uppercase_col_data('{{SRC}}')}} or enter {{uppercase_col_data('"CUSTOMER"."C_COMMENTS"')}}.
   1. SRC is a token that refers to the fully qualified source column name.
5. Validate the function. Then Create and Run to see the data as UPPERCASE.

{%- macro uppercase_col_data(column) -%}
   UPPER({{ column }})
{%- endmacro %}

Using a Macro with Another Macro

To keep your code from repeating itself, you can refer to another macro with another. In this example, we create a macro to remove dashes. Then another macro that uses remove_dashes and formats a phone number to standard American number, 123-456-7890.

This means the remove_dashes macro is available to be used and the format_phone_number can be used separately.

It uses the CUSTOMER.C_PHONE column.

1. To run this macro, go to Build Settings > Macros.
2. Copy and paste the macro there.
3. In your Node Editor, either double click on the Transforms field or select the column and open the Column Editor.
4. Enter {{format_phone_number('{{SRC}}')}} or enter {{format_phone_number('"CUSTOMER"."C_PHONE"')}}.
   1. SRC is a token that refers to the fully qualified source column name.
5. Validate the function. Then Create and Run to see the phone number formatted.

{% macro remove_dashes(column_name) %}
    REPLACE({{ column_name }}, '-', '')
{% endmacro %}

{% macro format_phone_number(column_name) %}
    CONCAT(
        LEFT({{ remove_dashes(column_name) }}, 3), '-',
        SUBSTRING({{ remove_dashes(column_name) }}, 4, 2), '-',
        RIGHT({{ remove_dashes(column_name) }}, 4)
    )
{% endmacro %}

Macros and Tokens

Macros can also use helper tokens. Helper tokens are shorthand in Coalesce that lets you refer to a column or node without needing to type the whole name.

For example, you have a macro named hello_goodbye.

• Longhand - {{hello_goodbye('"CUSTOMER"."C_PHONE"')}}
• Shorthand - {{hello_goodbye('{{SRC}}')}}. This uses SRC to refer to fully qualified source column name.

Check out Helper Tokens.

For fully qualified table, view, or procedure names, including {{this}} for the current Node, use the patterns in Ref Functions (not helper tokens alone).