Need some help with jinja trying to use a for loop over a run_query result set from a seed table

Help
1

Hi,

In the process of teaching myself dbt. Have run into a bit of a snag trying to iterate over a result set from a seed table. Seed table CSV is pretty basic here, just trying to prove the concept

The example code I’m following for this approach is taken from the dbt documentation on
using run_query(…) jinja function

1 New York
2 Florida
3 Oregon
4 New Mexico
5 Texas
6 Colorado 
{% set my_seed_query %}
select * from {{ ref('my_seed_table') }} 
{% endset %}
{% set results = run_query('select * from my_seed_query') %}
{% if execute  %}
{% set results_sv_list = results.columns[1].values() %}
{% else %}
{% set results_sv_list = [] %}
{% endif %} 
{% for item in results_sv_list %}
 {{ print(item) }} 
{% endfor  %}

I’ve tried some variation on this

with my_seed_query as (
 select * from {{ ref('my_seed_table') }}
)

{% set results = run_query('select * from my_seed_query') %}
{% if execute  %}
{% set results_sv_list = results.columns[1].values() %}
{% else %}
{% set results_sv_list = [] %}
{% endif %}
{% for item in results_sv_list %}
    {{ print(item) }}
{% endfor  %}

and also

{% set my_seed_query %}
 select * from {{ ref('my_seed_table') }}
{% endset %}
{% set results = run_query(my_seed_query) %}
{% if execute  %}
{% set results_sv_list = results.columns[1].values() %}
{% else %}
{% set results_sv_list = [] %}
{% endif %}
{% for item in results_sv_list %}
    {{ print(item) }}
{% endfor  %}

All of the above produce various errors

However, the code below works as expected and returns a result table, so the seed table is being resolved with ref(…) command, at least in the scenario below

with my_seed_query as (
 select * from {{ ref('my_seed_table') }}
)

select * from my_seed_query
2

hello
check the compiled version of that ninja - you would see ref() DOES explode the actual name - while run_query DOES NOT
plus as beginner join slack #advice-dbt-for-beginners Slack

3

It’s not clear what’s going on here, you’re trying to select * from a variable, try this
{% set results = run_query(my_seed_query) %}

4

This isn’t really enough to go on, so I’m going to skip over the sample code for now. Especially since it looks like your goal can be achieved via an existing library - the dbt utils package has a macro to get a column’s values: get_column_values

You can either use it directly, or you could have a look at its source code to see how it’s implemented

5

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