CODEWITHSUNDEEP
sqlmacrosdbt
Sowndarya Subramanian
Sowndarya Subramanian

Reputation: 21

How to call function in macro in DBT

I am new to dbt, so not sure how to do this. So I'm sending one of the columns to my macro from the model which in turn it will check for the match in the case-when present in that macro file.

So before it starts checking in all the case-when, I need to do some manipulation (split_part) on that particular column that I'm sending from the model.

Once this manipulation is done, then I need to send the altered field into the case when to check further Could someone help me in achieving this?

Sample code: macro_test.sql

{%- macro get_states(state_name) -%}

--- here I need to do some alteration like below on state_name ---

   split_part(state_name,'+',1)) as state_name_adjusted 

    CASE

     WHEN UPPER ({{ state_name_adjusted }}) IN ('XXX','YYY','ZZZ') THEN 'XXX' :: VARCHAR(64)

     WHEN UPPER ({.... so on....

    END

{%- endmacro -%}

Upvotes: 1

Views: 6119

Answers (1)

Branden Ciranni
Branden Ciranni

Reputation: 492

This should work.

{%- macro get_states(state_name) -%}

    {%- set state_name_adjusted -%}
        split_part({{state_name}},'+',1)
    {%- endset -%}

    CASE

        WHEN UPPER ({{ state_name_adjusted }}) IN ('XXX','YYY','ZZZ') THEN 'XXX' :: VARCHAR(64)

        WHEN UPPER ({.... so on....

    END

{%- endmacro -%}

For example, if you compile an example:

{% set state_name = 'example_state' %}

{%- set state_name_adjusted -%}
    split_part({{state_name}},'+',1)
{%- endset -%}

CASE

    WHEN UPPER ({{ state_name_adjusted }}) IN ('XXX','YYY','ZZZ') THEN 'XXX' :: VARCHAR(64)

    WHEN UPPER ({.... so on....

END

you would get

CASE

    WHEN UPPER (split_part(example_state,'+',1)) IN ('XXX','YYY','ZZZ') THEN 'XXX' :: VARCHAR(64)

    WHEN UPPER ({.... so on....

END

Upvotes: 1

Related Questions