basic macro question

Help
1

Hi,
I’m kind of tearing my hair out on this one and I don’t understand what I’m doing wrong. The basic use case is I have some pretty substantial business logic I’m trying to implement in a view. the logic is branching and has a few layers and is also very likely to evolve a bit over time. My feeling is that the sane and sustainable way to do it would probably be a macro.

So In trying to get my feet wet on this, I’ve created this:

{% macro test_my_field(input) %}  

    {% if input == 'F' %}  
        'yes'
    {% else %}  
        'no'
    {% endif %}  
{% endmacro %}

It doesn’t error out, but I’m struggling to get it to function correctly in practice.

For example, I pass a field to it from a query like:
select
somefield,
someotherfield
{{test_my_field(‘somefield’)}} as example
from sometable

It will return the false part always even when the contents of somefield are F. Although when change the argument explicitly to {{test_my_field(‘F’)}} it will return true.

Things I’ve tried:
-Various quotes/single quotes within the macro.
-Setting the test case to a variable explicitly declared as string.
-Testing to make sure the macro is actually receiving a single character ‘F’ from the calling query.

2

Content of your macro should be

case when = ‘F’ then ‘yes’ else ‘no’ end

Note: @Hannes originally posted this reply in Slack. It might not have transferred perfectly.

3

I found <postgresql - How to Pass Column Values to DBT Macro When Column Names Have '.' Characters - Stack Overflow explanation> helpful when I got tripped up on this

Note: @Alyssa K originally posted this reply in Slack. It might not have transferred perfectly.

4

Dan is trying to solve the problem in jinja but he has to solve it in SQL

Note: @Hannes originally posted this reply in Slack. It might not have transferred perfectly.

5

Jinja just injects the column for keeping it dry

Note: @Hannes originally posted this reply in Slack. It might not have transferred perfectly.

6

Hi! Reminder: “guys” is an inherently gendered term. Great alternatives include “everyone”, “folks”, “team”, “friends” or “y’all”. Thank you for helping us build a more inclusive community!

Note: @Slackbot originally posted this reply in Slack. It might not have transferred perfectly.

7

thanks guys.
<@U04N2MCHG58>: what do you mean this problem should be solved in SQL? I guess I was thinking this use case would be ideal for a UDF of some sort since the logic is going to be rather extensive , but of course that’s just my assumption.

Note: @danloz originally posted this reply in Slack. It might not have transferred perfectly.

8

I mean that inside your macro has to be mostly SQL (case when) and not jinja ({%if%}). You can check your different results in the target compile folder

Note: @Hannes originally posted this reply in Slack. It might not have transferred perfectly.