selecting where in multiple columns on ANSI SQL (IMPALA SQL)

Question

It worked normally in Oracle SQL, but it does not work in ANSI SQL.

SELECT whatever WHERE (col1,col2) IN ((val1, val2), (val1, val2), ...)

How do I write code in ANSI SQL (IMPALA SQL)?

I don't want the following code because there are many lists.

WHERE (col1 = val1a AND col2 = val2a)
   OR (col1 = val1b AND col2 = val2b)
   ...

thank you!

(ex) https://dba.stackexchange.com/questions/34266/selecting-where-two-columns-are-in-a-set

We can do smooth operation in ANSI sql.

Answer 1

This isnt possible in hive or impala. Only 'other' workaround is concat().
You can use below sql-

...
where concat(col1,'~',col2) IN (concat(val1,'~',val2),concat(val3,'~',val4)...)

Pls note if col1/col2 is null, it wont be matched.

EDIT : this can have severe performance problem. So, you can store val1,val2 in a static/lookup table and then use it to join with main table like this -

select ...
from table1 t1
join table2 t2 on t1.col1=t2.val1 and t1.col2=t2.val2

You also have flexibility to change values as per future need and not change the sql.