Home » SQL & PL/SQL » SQL & PL/SQL » SQL query doubt ..
SQL query doubt .. [message #198698] Wed, 18 October 2006 05:06 Go to next message
priya.vmr
Messages: 5
Registered: October 2006
Junior Member
Hi everyone,

I have one simple doubt. Plz prove it.

If this is possible

SQL> select * from emp
2 where (empno,ename) in (select empno,ename from emp where empno=7369 and ename='SMITH');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20

SQL>


then why not the below. Can anyone plz expalin

SQL> select * from emp where (empno,ename) in (7369,'SMITH');
select * from emp where (empno,ename) in (7369,'SMITH')
*
ERROR at line 1:
ORA-00920: invalid relational operator


Thanks in advance,
Priya
Re: SQL query doubt .. [message #198705 is a reply to message #198698] Wed, 18 October 2006 05:21 Go to previous messageGo to next message
jkmurthy
Messages: 22
Registered: May 2005
Location: India
Junior Member

Hi Priya,

When comma-seperated static values are enclosed in parantheses, Oracle assumes that they are being compared against a single column.

Your query can however be modified as:

SQL> select * from emp where (empno,ename) in
2 (select 7369,'SMITH' from dual);

Hope this helps.

Cheers
Keshav
Re: SQL query doubt .. [message #198709 is a reply to message #198698] Wed, 18 October 2006 05:33 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi
TRY THIS
select * from scott.emp where empno='7369' AND ename='SMITH'
regards

Muhammad Shahid Mughal

[Updated on: Wed, 18 October 2006 05:37]

Report message to a moderator

Re: SQL query doubt .. [message #198720 is a reply to message #198705] Wed, 18 October 2006 06:04 Go to previous messageGo to next message
priya.vmr
Messages: 5
Registered: October 2006
Junior Member
Thanks and lot
Re: SQL query doubt .. [message #198725 is a reply to message #198698] Wed, 18 October 2006 06:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Priya,
In your example you are using pairwise comparison and IN clause.

So, the statement should be as follows.

select * from emp where (empno,ename) in ((7369,'SMITH'));


One parenthesis for IN clause and the other for pairwise comparison.

Hope I'm clear.

By,
Vamsi.
Re: SQL query doubt .. [message #198747 is a reply to message #198725] Wed, 18 October 2006 07:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
vamsi kasina wrote on Wed, 18 October 2006 13:18
One parenthesis for IN clause and the other for pairwise comparison.
Amen to that! Good explanation.

MHE

[Updated on: Wed, 18 October 2006 07:32]

Report message to a moderator

Re: SQL query doubt .. [message #198753 is a reply to message #198747] Wed, 18 October 2006 07:43 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Thanks Maaher,
But I really didn't understand the following.
select * from emp where (empno,ename) = ((7369,'SMITH'));

is working fine. And the following is giving the error
select * from emp where (empno,ename) = (7369,'SMITH');

ERROR at line 1:
ORA-00920: invalid relational operator

By
Vamsi.
Re: SQL query doubt .. [message #198759 is a reply to message #198753] Wed, 18 October 2006 08:05 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You said it yourself: One parenthesis for IN clause and the other for pairwise comparison. The inner parentheses group the two values. The base is:
...where <a> in (<x1>,<x2>)
If <a> is a single column, you can match it with single values for <x1>,<x2>,... .If <a> is a set of columns, say (empno, ename) then <x1> and <x2> should also be a set of values. Those sets need to be in parentheses, just like the column list you want to compare. So, for <a> = (empno, ename), this becomes:
select <column_list>
from  employees
where 
      (empno, ename) -- <a>
                     in ( 
                          (1,'SMITH') -- <x1>
                        , (2,'JONES') -- <x2>
                        )


MHE
Re: SQL query doubt .. [message #198773 is a reply to message #198759] Wed, 18 October 2006 08:34 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Sorry, I've misread the previous post. I'm sorry but I don't know why Oracle requires the extra parantheses.

For equasions, you rather use an 'AND' construction. I have never, and I really do mean never, used an equasion like in your post.

MHE
Previous Topic: Constraint Naming
Next Topic: avg( ) over (partition by )
Goto Forum:
  


Current Time: Sat Dec 10 04:58:40 CST 2016

Total time taken to generate the page: 0.12915 seconds