I have this code, it takes max value from the table

{%- call statement('my_statement', fetch_result=True) -%}
      select max(CAST(updated AS bigint)) as val from table_1
{%- endcall -%}
{%- set my_var = load_result('my_statement')['data'] -%}

{{ return(my_var) }}

the return variable I have

[(Decimal('1655275144000'),)]

I am trying to get only the number from this variable. When I write like this, I have an error

{%- set my_var = load_result('my_statement')['data'][0][0] -%}

although in python with importing the library it works great

How can I get this number in dbt with jinja

CodePudding user response：

{%- call statement('my_statement', fetch_result=True) -%}
      select max(CAST(updated AS bigint)) as val from table_1
{%- set my_var = load_result('my_statement')['data'] -%}
{{ return(my_var) }}
{%- endcall -%}

CodePudding user response：

There could be a few different things going on here. Your code looks okay, and I couldn't reproduce your exact error. There are a few ways to go awry:

1. If you are using load_result inside of a model, or in a macro that gets called by a model, you need to wrap that in an {% if execute %} block; otherwise it can create parsing/compilation errors. See the docs.
2. I want to be clear that you're evaluating {%- set my_var = load_result('my_statement')['data'][0][0] -%} in the same macro or model where you define my_statement?

Finally, I would recommend using the newer run_query macro, instead of the call statement... load_result pattern, since it's a little less confusing. See the docs for run_query