Have a dynamic parameter as a JS function

[caracena]

Hello everybody,

We are trying to start using Dataform to orquestate and versionize our BigQuery queries but in the early tests I find that many of the logic we use in most of our queries cannot be used in DF. For example, one thing that is very common for us is to catch the last character/digit of a string or number and make comparisons on the fly. For example return a boolean if it's even or false if it's odd.

Normally in BQ I would use the function RIGHT() (with the parameter 1) to get the last character and use that inside a CASE operation. What I'm trying to achieve is using the result of RIGHT() inside a custom function written in JavaScript (inside the SQLX file itself or in a modules.js file under includes) to turn the result into a boolean.

I've tried many different approaches. The last iteration is the following:

Even or odd function:

js {
    function is_even(n) {
        let res = false;
        if (n % 2 == 0) res = true;
        return res;
    }
}

 SQL:

SELECT ${is_even(RIGHT(id, 1))} AS even_or_odd
FROM ${ref("step-01")}

The result is always one of two: It doesn't find the field "id" from the source of, like in this case, it doen't find the function RIGHT. It also doesn't work nesting custom functions (i've also created one to replace RIGHT()).

One more thing. I've tried with types "table", "view" and "operations". Nothing. Is it possible?

Thanks in advance!