OraFAQ Forum: SQL & PL/SQL » Looking for a proper search term

Home » SQL & PL/SQL » SQL & PL/SQL » Looking for a proper search term

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Looking for a proper search term [message #294124]

Wed, 16 January 2008 09:43

Frank
Messages: 7901
Registered: March 2000

Senior Member

I want to search for a DB/2 equivalent for this construct:
where (col1, col2) in ((val1, val2), (val3, val4))

so, the comparison on multiple columns at a time.
Does anybody know the name of this construct? I can't find a proper search-term.

Report message to a moderator

Re: Looking for a proper search term [message #294128 is a reply to message #294124]

Wed, 16 January 2008 09:52

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

I think the term you are looking for is pairwise

Report message to a moderator

Re: Looking for a proper search term [message #294134 is a reply to message #294124]

Wed, 16 January 2008 10:20

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And this is a valid syntax in Oracle:

SQL>select * from emp where (empno,deptno) in ((7839,10),(7698,30));

EMPNO ENAME JOB        MGR HIREDATE  SAL COMM DEPTNO 
 7698 BLAKE MANAGER   7839 01/05/81 2850    -     30 
 7839 KING  PRESIDENT    - 17/11/81 5000    -     10

Regards
Michel

Report message to a moderator

Re: Looking for a proper search term [message #294170 is a reply to message #294134]

Wed, 16 January 2008 11:33

Frank
Messages: 7901
Registered: March 2000

Senior Member

Sorry Michel, missing your point.
Of course I know it is valid syntax, why else would I mention it.
I need to do the same in a DB/2 database(or find something equivalent that works both in DB/2 and Oracle).

Report message to a moderator

Re: Looking for a proper search term [message #294174 is a reply to message #294170]

Wed, 16 January 2008 12:02

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Sorry Frank, I misunderstood your question, I thought you said the opposite: this works in DB2 and you wanted to know how to do it in Oracle.

Regards
Michel

Report message to a moderator

Re: Looking for a proper search term [message #294177 is a reply to message #294124]

Wed, 16 January 2008 12:21

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Oracle refers to this as a "multi-column in-list" in some of their examples:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/qradv.htm#sthref901

But, that may not be what they call it in DB2 and there may not be a comparable method. I don't know DB2.

Report message to a moderator