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.)

Help
,
1

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.

"(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.

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()”.

{% 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,

2

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 .

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.

{% 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
Macro_return_error763×573 8.03 KB

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

Thanks,
Yogesh