call statement inside macros returns None

Help
, ,
1

call statement inside macros returns None

Im trying to set variable using sql query (also using other set variables from before), it works fine outside of macros, but as soon as i put it in macros, the call statement returns None, so when i try to get result[‘Data’][0][0], I get an error of ‘None’ has no attribute ‘data’

  • Ive tried passing datekey in macros manually by setting it inside as variable
  • ive trying executing same querry manyally and executing same logic outside of macros and it works fine
  • ive tried logging and checking if previous variables i use inside of query are being set as they are supposed to be and they are all fine

the code inside macros

{% macro mcr_GetUserAmountByFreezingDate(datekey) %}

    {%set CalculationDate = mcr_DateKey_to_Date(datekey)%}
    {%set StartDateKey = mcr_Start_of_Month_Datekey(datekey)%}

    {%- call statement('my_statement', fetch_result=True) -%}
        SELECT MAX(FreezingDate) as max_date
        FROM {{source ('dwp_input','FreezingDate')}} (NOLOCK)
        WHERE FreezingEndOfMonth <= EOMONTH({{CalculationDate}})
    {%- endcall -%}

    {%- set max_date = "'" ~ load_result('my_statement')['data'][0][0] ~ "'" -%}
    {%set FreezingDateKey = mcr_DateTime_to_DateKey(max_date)%}
    SELECT {{FreezingDateKey }} as frz_datekey

{% endmacro %}

the code to call macro

{{mcr_GetUserAmountByFreezingDate('20240930')}}

the code that works outside macro

{%set datekey = 20240930%}

{%set CalculationDate = mcr_DateKey_to_Date(datekey)%}
{%set StartDateKey = mcr_Start_of_Month_Datekey(datekey)%}

{%- call statement('my_statement', fetch_result=True) -%}
    SELECT MAX(FreezingDate) as max_date
    FROM {{source ('dwp_input','FreezingDate')}} (NOLOCK)
    WHERE FreezingEndOfMonth <= EOMONTH({{CalculationDate}})
{%- endcall -%}

{%- set max_date = "'" ~ load_result('my_statement')['data'][0][0] ~ "'" -%}
{%set FreezingDateKey = mcr_DateTime_to_DateKey(max_date)%}

SELECT {{FreezingDateKey}} as frz_max

This last code snippet returns the result just fine, if anyone could help me solve this error i would greatly appreciate it, thanks in advance!

2

Just verifying that both the source table is named ‘FreezingDate’ and the column you want the max of is named ‘FreezingDate’?

Note: @Renee originally posted this reply in Slack. It might not have transferred perfectly.

3

Yes, i just found the problem and came here to post, so the issue is fixed by placing the logic of macros inside an if statement to check if the macros is in the execute state,
{% if execute %}
code
{% endif %}

…like this