as_scalar isn't working

Hi I’m trying to store a scalar value in a variable and using in select statement.

What I’ve already tried

Some example code or error messages

set etl_batch_id_var = 
        "select nvl(max(etl_batch_id) + 1, 1000) as etl_batch_id from {{ this }}"
     | as_scalar , %}

It gives me a compilation error that says as_scalar is not a filter. Is there is any alternate to it?

Hi @rjames

run_query returns a table, you have to specify the column and row you want.

To do that you can return the column as a list, have a look at this example:

{% set payment_methods_query %}
select distinct payment_method from app_data.payments
order by 1
{% endset %}

{% set results = run_query(payment_methods_query) %}

{% if execute %}
{# Return the first column #}
{% set results_list = results.columns[0].values() %}
{% else %}
{% set results_list = [] %}
{% endif %}

{% for payment_method in results_list %}
sum(case when payment_method = '{{ payment_method }}' then amount end) as {{ payment_method }}_amount,
{% endfor %}
sum(amount) as total_amount
from {{ ref('raw_payments') }}
group by 1

source: run_query | dbt Developer Hub

But instead of selecting the whole column you can select the first item results_list[0].

Additionally, I have not found the as_scalar filter, did you mean as_number?

Thank you @brunoszdl My query returns a scalar value. like max of date. Do i still need to follow these steps. Is there is a way to get the resultset stored directly into a variable as it a scalar resultset?

@rjames , even if your select returns a scalar, the run_query function returns a table, with one column and one row.

So, yes, the only way I know is by doing all of these steps, don’t know a workaround.

If you want to keep it all in one line you try something like

{% set your_var = run_query("select nvl(max(etl_batch_id) + 1, 1000) as etl_batch_id from {{ this }}").columns[0].values()[0] %}

Maybe you have to put it inside a {% if execute() %} condition like

{% if execute %}
{% set your_var = run_query("select nvl(max(etl_batch_id) + 1, 1000) as etl_batch_id from {{ this }}").columns[0].values()[0] %}
{% endif %}

But try without it first.

I did not test it, so maybe you need to adjust something.

I would recommend separating it into multiple lines, so it is easier to debug.

{% set your_query %}
select nvl(max(etl_batch_id) + 1, 1000) as etl_batch_id from {{ this }}
{% endset %}

{% set results = run_query(your_query) %}

{% if execute %}
{% set results_list = results.columns[0].values() %}
{% endif %}

{% set result = results_list[0] %}
1 Like

@rjames I think you’re trying to use an Ansible filter - they have extended Jinja beyond what comes out of the box.

check out get_single_value from dbt utils, it will abstract all of this away for you


@joellabes, didn’t know this macro, much better than returning a table to get the single value :sweat_smile:, thank you!!

1 Like

Thank you @joellabes and @brunoszdl

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.