multiple columns with multiple values in where clause

Question

I am executing a query which has multiple columns in where clause which has multiple values. I know that in SQL you can use IN condition to satisfy and get the correct output. What is the way to do in teradata?

my code 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

My query in teradata looks like this

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

This however returns more rows:

country_code  phone_num  
91            1234567890
91            1020304050
44            1020304050
1             998877446655

NOTE: The combination of country_code and phone num is not unique.

Is there a way to filter it out in teradata like that in ORACLE?

Answer 1

As far as I know, Teradata does not support the "expanded" where clause syntax as you can do in Oracle; you need to specify the criteria as compound expressions:

select country_code ,phone_num
from telephone_directory
where (country_code=91 and phone_num=1234567890)
   or (country_code=44 and phone_num=1020304050)
   or (country_code=1  and phone_num=998877446655)

Answer 2

select USER_TYPE,USER_ID
from USER_TABLE
where (USER_TYPE || USER_ID) in (('F6713'),('S1178'),('M5715'),('F8341'),('F1284'))