macro return value

Help
1

I have below macro

{% macro get_department_name(value) %}

    {% set test_sql %}
        select deptid, description from {{ ref('raw_department') }} where deptid = '{{value}}'
    {% endset %}
 
    {% set results = run_query(test_sql) %}

    {% if execute %}
        {% for row in results.rows %}
            {%- set retval = row[1] -%}
        {% endfor %}
    {% endif %}
    {{return(retval)}}
{% endmacro %}

I am calling the above macro from a select statement

select e.deptid,
{{macro_hello_world_5('e.deptid')}}
from {{ ref('raw_employee') }} e

I am getting below error
“Database Error in rpc request (from remote system.sql) 001003 (42000): SQL compilation error: syntax error line 3 at position 0 unexpected ‘from’.”

compiled code looks like below:

select e.deptid,
from ANALYTICS.dbt_vsnowflake.raw_employee e
2

I think replacing {{return(retval)}} with {{retval}} will mean a value is templated into your query, but I’m not convinced that will do what you want. Why are you using Jinja vs just joining in SQL?

Note: @Josh Devlin (he) originally posted this reply in Slack. It might not have transferred perfectly.

3

I agree with Josh - this should be done in standard SQL. This macro’s query will not execute for every variant of the e.deptid - Jinja macros are evaluated and resolved as a static string during the compilation stage.

Instead your code should be:

select 
  e.deptid,
  d.description
from {{ ref('raw_employee') }} e
inner join {{ ref('raw_department') }} d 
  on e.deptid = d.deptid
4

@ck82 looks like ur accessing the variable retval outside of the if block but it was initialized in sidefor loop. The scope of the variable retval is limited to for loop

5

This is true, but to be clear: fixing that won’t make the macro behave the way you want

6

Got it, Thanks @joellabes

7

Hi Joellabes,
Agree, this is one of the solutions. I was experimenting on macros and want to see the “return” behavior in macros and experimented this way.
we generally call a function in oracle/snowflake and implement the same logic like I did in this macro to decrease the number of joins in the calling sql, so tried the similar way here.
So, the way I am trying to use the macro to return a value is wrong and dbt wouldnt support this?

8

Macros aren’t the same as UDFs - they are rendered once and return a static string of SQL.

If you want to create a UDF in dbt, check out Using dbt to manage user defined functions