# How can we pass column values (filtered after where clause) to a macro in select clause of the sql query?( so that macro should run iteratively for those values.)

**URL:** https://discourse.getdbt.com/t/how-can-we-pass-column-values-filtered-after-where-clause-to-a-macro-in-select-clause-of-the-sql-query-so-that-macro-should-run-iteratively-for-those-values/9430
**Category:** Help
**Tags:** jinja, dbt-core
**Created:** [August 5, 2023, 5:49pm UTC](https://discourse.getdbt.com/t/how-can-we-pass-column-values-filtered-after-where-clause-to-a-macro-in-select-clause-of-the-sql-query-so-that-macro-should-run-iteratively-for-those-values/9430 "2023-08-05T17:49:45Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![Yogesh](https://avatars.discourse-cdn.com/v4/letter/y/49beb7/32.png) [@Yogesh](https://discourse.getdbt.com/u/Yogesh)
#### Post date: [August 5, 2023, 5:49pm UTC](https://discourse.getdbt.com/t/how-can-we-pass-column-values-filtered-after-where-clause-to-a-macro-in-select-clause-of-the-sql-query-so-that-macro-should-run-iteratively-for-those-values/9430/1 "2023-08-05T17:49:45Z")

</div>

Hi Community,

## The problem I’m having

Actually I am working on Migration project , where the code is written in oracle sql and which is integrated through ODI (oracle data integrater ) as ETL tool. Now I am trying to convert that code compatible to dbt and Hive database.

In oracle, code looks simple as below. In below sql, package and function is being called altogether “xx\_stg\_pkg.get\_desc(c.book\_type\_code)” as the function is defined and written in separate package sql file. hence It is getting called for each value of c.book\_type\_code column filtered after where clause. It’s working fine in oracle.

```auto
"(select b.book_type_code as code ,
(xx_stg_pkg.get_desc (c.book_type_code)) book_desc,
from fa_books b , fa_distribution_history c
where b.book_type_code = c.book_type_code ))"

```

## The context of why I’m trying to do this

Now, I want to do it in a same way as how oracle and ODI did it.  
I have written one model sql as below.

```auto
with final as 
(
select b.book_type_code as code ,
        {{ get_desc (c.book_type_code) }} book_desc,
from fa_books b , fa_distribution_history c 
where b.book_type_code = c.book_type_code )
 
Select * from final

```

and calling below macro “get\_desc()” inside above Model sql in select clause of the query. In select clause I am trying to pass ‘c.book\_type\_code’ as column (values filtered after where clause) to macro “get\_desc()”.

```auto
{% macro get_desc(book_code) -%}
   {%- if execute %}   
   
{{ log(" book_code returned from function :"~book_code, info=True) }}    
               {{return(book_code)}}
 
   {% endif %}   
{%- endmacro -%}

```

When I am trying to pass c.book\_type\_code as variable to macro get\_desc(). It’s giving error in compilation stage as ‘c is undefined’. I tried doing it multiple possible ways but its not working here as oracle. I left with no other option to call macro get\_desc() in for loop manually considering each value of c.book\_type\_code column filtered after where clause.

Suppose, If Model sql results into 10 rows after where clause , then macro “get\_desc()” should run for ten times considering 10 values of c.book\_type\_code.

Please help me to achieve this in dbt.

Thanks,

---

<div class="post-metadata">

### Author: ![Yogesh](https://avatars.discourse-cdn.com/v4/letter/y/49beb7/32.png) [@Yogesh](https://discourse.getdbt.com/u/Yogesh)
#### Post date: [August 8, 2023, 8:17pm UTC](https://discourse.getdbt.com/t/how-can-we-pass-column-values-filtered-after-where-clause-to-a-macro-in-select-clause-of-the-sql-query-so-that-macro-should-run-iteratively-for-those-values/9430/2 "2023-08-08T20:17:30Z")

</div>

I also tried resolving above mentioned problem statement using adapter.dispatch method.

In below Model sql, c.book\_type\_Code is being passed as an argument to macro as below .

```auto
final as
(
select b.book_type_code as code ,
{{ return (adapter.dispatch('get_desc')(c.book_type_code)) }} book_desc
from fa_books b , fa_distribution_history c
where b.book_type_code = c.book_type_code )
 
Select * from final

```

If dbt does not find an adapter-specific implementation, it will dispatch to the default implementation as below.

```auto
{% macro default__get_desc(book_code) -%}
    {%- if execute %}
    {{ log(" book_code returned from function :"~book_code, info=True) }}
    {{return(book_code)}}
    {% endif %}
{%- endmacro -%}

```

But getting an error as “dbt.exceptions.MacroReturn” PFA error snapshot.

 ![Macro_return_error](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/6/61aa0547fa5339328ed54683d95b70093df56552.png)

Please let know me, whether is it right approach to use adapter.dispatch method and also please help me toresolve above error.

Thanks,  
Yogesh