error when using variable of marcos

I have a problem, there is a macro in which I calculate the date and want to pass it to the model

{% macro get_report_date() %}
{% if execute %}
{% set p_date = run_query("select  max(report_date) as report_date  from dwh_dq.dwh_schedule ds where status=1" ).columns[0][0] %}
{% else %}
{% set p_date = '2024-05-01' %} 
{% endif %}
{%do return(p_date)%}
{% endmacro %}

in the model I declare a variable and assign the result of the macro to it in order to use this variable in pre_hook

{% set p_date_v2 = get_report_date() %}
       "{{ insert_dwh_log(schema_name='dwh_dds',table_name='organizations_type',source_name='BOS',report_date=p_date_v2,pre_hook=True) }}"

and I got an error
invalid input syntax for type date: “”
LINE 4: …organizations_type’ and source_name=‘BOS’ and report_date=‘’
like it doesnt see the date
but if I call marcos in pre_hook it’s work correctly

"{{ insert_dwh_log(schema_name='dwh_dds',table_name='organizations_type',source_name='BOS',report_date=get_report_date(),pre_hook=True) }}"

it see the date 2025-05-02.

Someone please explain me what I do wrong???

In macros insert_dwh_log it doesnt see the value of variable, I dont know why


1 Like