29 May 18 ·  nVector in teradata ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

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 18

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds