29 May 2018        Add to Favorites   Report

How to filter with multiple columns with multiple values in where clause

Scenario: We need to execute a query which has multiple columns in where clause which has multiple values

Syntax in Oracle looks like this:

select td.country_code,td.phone_num 
from telephone_directory td 
where (td.country_code, td.phone_num) in ((91,1234567890),(44,1020304050),(1,998877446655))

This prints out the exact result i.e. 3 rows

However we cannot specify multiple column pairs in where clause in Teradata

So, first we need to load those column value pairs to a volatile table, eg: VT_VALUES and use it like a regular join.

select country_code ,phone_num  
from telephone_directory 
where (country_code,phone_num ) in (select country_code,phone_num from VT_VALUES);