CODEWITHSUNDEEP
dbt
user17084215
user17084215

Reputation: 51

dbt macro to extract MAX len of column

I essentially want to pass in a column, then do a calculation that automatically calculates the numbers that I will eventually pass in to my CAST as Decimal(x,x) argument so I am always accounting for the largest Decimal so no rounding occurs.

I want to pass in a target column split on the period, calculate the max len() to the left, and then the max len() to the right, and then return (left+right, right) so it is something like 22,8.

Here is my macro so far:

{% macro cast_decimal(max_field, table_name) %}
    {%- call statement('cast_decimal_max', fetch_result=True) -%}
        
        WITH mq AS
        (
        SELECT MAX(len(split_part({{ max_field }},'.',1))) AS max_l,
               MAX(len(split_part({{ max_field }},'.',2))) AS max_r
        FROM {{ table_name }}
        )
        SELECT (max_l + max_r) || ',' || max_r AS max_total
        FROM mq

    {%- endcall %}
    {%- set max_existing_total = load_result('cast_decimal_max').table.columns['max_total'].values()[0] -%}
    {{ return(max_existing_total) }}
{%- endmacro %}

I keep getting this error: 'None' has no attribute 'table' and I am not sure what I am doing wrong.

Bonus points if you can tell me how to automatically pass in the current table from my FROM statement so I do not need it as an argument in my macro, instead of doing it like this:

SELECT {{ cast_decimal(close_price, public.stable_prices) }}
FROM public.stable_prices

Upvotes: 4

Views: 2154

Answers (2)

user17084215
user17084215

Reputation: 51

{% macro cast_decimal(max_field, table_name) %}
{%- call statement('cast_decimal_max', fetch_result=True) -%}

    WITH mq AS
    (
    SELECT MAX(len(split_part({{ max_field }},'.',1))) AS max_l,
           MAX(len(split_part({{ max_field }},'.',2))) AS max_r
    FROM {{ table_name }}
    )
    SELECT (max_l + max_r) || ',' || max_r AS max_total
    FROM mq

{%- endcall %}
{% if execute %}
    {%- set max_existing_total = "'" ~ load_result('cast_decimal_max').table.columns['max_total'].values()[0] ~ "'"-%}
    {{ return(max_existing_total) }}
{% else %}
    {{ return(false) }}
{% endif %}
{%- endmacro %}

This solution works- Wrapping the results with "'" ~ and adding the if execute statement.

Upvotes: 1

David Clarance
David Clarance

Reputation: 528

I can't test this since I don't have access to a postgres db (which I assume you're using?). What happens if you put your arguments in quotes in your call in the script? So like this,

SELECT {{ cast_decimal('close_price', 'public.stable_prices') }}
FROM public.stable_prices

(sorry this should have been a comment but can't add comments yet!)

Upvotes: 0

Related Questions