/*
if <value> is NULL:
return "N"
else:
if <value> is an integer returns 'Y'
elif upper <value> = 'NULL' return NULL
else 'Y'
Code sample:
{{ nvl_flag('r.column_name') }} AS Column_Name,
*/
{% macro nvl_flag(column_name) %}
case
when {{ column_name }} is null then 'N'
else
case
when {{ column_name }} = True OR UPPER({{ column_name }}) = 'TRUE' then 'Y'
when {{ column_name }} = False OR UPPER({{ column_name }}) = 'FALSE' then 'N'
when IS_REAL(TO_VARIANT({{ column_name }})) = True then 'Y'
when UPPER({{ column_name }}) = 'NULL' then 'N'
when UPPER({{ column_name }}) = 'YES' then 'Y'
when UPPER({{ column_name }}) = 'NO' then 'N'
else to_varchar({{ column_name }})
end
end
{% endmacro %}