For example, let's immagine a "match_equals" function in Pinot that accepts 3 parameters: 1. A test number 2. A true number 3. A false number then if the test number matches the value in the current row/column, the true number is given, otherwise the false number is given.

The "true" and "false" numbers here would always be 1 and 0, actually. The thing is, I want to be able to do something like this:

select match_equals(12, 1, 0), match_equals(23, 1, 0), match_equals(34, 1, 0)

and then get

(1, 0, 0)

if the number matches 12,

(0, 1, 0)

if it matches 23 and

(0, 0, 1)

if it matches 34. Is there something I can use to get a result like this?

The reason why I want to do this is because if I have 4 rows where 2 of them match

12

, 1 of them matches

23

and none of them matches

34

, I need to sum them up and have a result like

(2, 1, 0)

I just found

arrayIndexOfInt

which I can use to hack my way through, just don't know if this will perform well though...

@Xiaobing am I doing something wrong? This query doesn't run for me:

SELECT (CASE WHEN 9 = 9 THEN 1 ELSE 0 END) AS is_unknown FROM ignoreMe

or is it just because I'm not using it with real data?

@Xiaobing works like a charm! Thanks a lot! 🙂