29 May 2018 · npac ·       Add to Favorites   Report

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

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);


posted on 29 May 2018

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too