Hi I’m trying to store a scalar value in a variable and using in select statement.
set etl_batch_id_var =
run_query(
"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 %}
select
order_id,
{% 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] %}
@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 
, thank you!!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.