Home » Other » Training & Certification » help
help [message #17823] Tue, 01 February 2005 14:24 Go to next message
zach
Messages: 7
Registered: January 2005
Junior Member
could u help figure out what's wrong with the following codes?

UPDATE student
SET rowcounter = rownum
WHERE snumber IN (SELECT snumber FROM student WHERE snumber < '11324' ORDER BY snumber);

It says: ORA-00907: missing right parenthesis

If the ORDER BY is deleted, it works fine. but I need to order it by snumber though.
Re: help [message #17824 is a reply to message #17823] Tue, 01 February 2005 14:50 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The ORDER BY clause is not valid in that subquery.

Storing order in the database - bad idea. You can always generate a "rowcounter" at runtime using either an analytical function or an inline view with rownum.
Re: help [message #17828 is a reply to message #17823] Tue, 01 February 2005 20:38 Go to previous messageGo to next message
Sreedhar Reddy
Messages: 55
Registered: January 2002
Member
distinct keyword will bydefault order the snumber column

UPDATE student
SET rowcounter = rownum
WHERE snumber IN (SELECT distinct snumber FROM student WHERE snumber < '11324' );
Re: help [message #17829 is a reply to message #17828] Tue, 01 February 2005 21:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The ordering in the subquery has no effect AT ALL:
the in-clause means snumber should be 1 or 2 or 3. This does not differ from an unordered list: snumber should be 3 or 1 or 2.
So, there is absolutely no need for the order by.

hth
Re: help [message #267261 is a reply to message #17829] Wed, 12 September 2007 21:07 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
i also faced the same problem while using follwing query

select ename,sal from emp where sal in(select losal from salgrade order by losal desc where rownum<=2)

i wanted the name of employees from emp whose salary lies in two highest losal values from salgrade...

please tell how to do as oracle gives following error..

select ename,sal from emp where sal in(select losal from salgrade order by losal where rownum<=2)
*
ERROR at line 1:
ORA-00907: missing right parenthesis

thanks a lot

ashish




hi friends.. ca we do the above query using joins.........

plz answer
thanks

[Updated on: Thu, 13 September 2007 11:59]

Report message to a moderator

Re: help [message #267267 is a reply to message #17824] Wed, 12 September 2007 22:29 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

select ename,sal from emp where sal in(select losal from salgrade order by losal where rownum<=2)
*
ERROR at line 1:
ORA-00907: missing right parenthesis



Answer is

Todd Barry wrote on Tue, 01 February 2005 14:50
The ORDER BY clause is not valid in that subquery.


Re: help [message #267270 is a reply to message #267261] Wed, 12 September 2007 22:37 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
ashish2345 wrote on Wed, 12 September 2007 21:07

i wanted the name of employees from emp whose salary lies in two highest losal values from salgrade...



For find out salaries lies in higest losal you have to use between.

Try with the following

SELECT ename, sal
  FROM emp, (SELECT MAX (losal) maxx, MIN (losal) minx
               FROM (SELECT RANK () OVER (ORDER BY losal DESC) rn, losal
                       FROM salgrade)
              WHERE rn <= 2) sal_ran
 WHERE sal BETWEEN sal_ran.minx AND sal_ran.maxx


Re: help [message #267323 is a reply to message #17828] Thu, 13 September 2007 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Sreedhar Reddy,

1/ distinct is useless in subbquery just a waste of time
2/ it is wrong to say that distinct orders.

Regards
Michel
Re: help [message #267339 is a reply to message #267261] Thu, 13 September 2007 01:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ashish2345 wrote on Thu, 13 September 2007 04:07

select ename,sal from emp where sal in(select losal from salgrade order by losal where rownum<=2)

Look at the order of the elements of your subquery, first order by, then where.
That's an error, no matter if the order by would be accepted or not.
Re: help [message #267509 is a reply to message #267339] Thu, 13 September 2007 10:08 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
1 select ename,sal from emp
2 where sal in
3* (select losal from salgrade where rownum<=2 order by losal)
4 /
(select losal from salgrade where rownum<=2 order by losal)
*
ERROR at line 3:
ORA-00907: missing right parenthesis



i tried above too.. so it can be done by rank function. thanks alot buddies. i am really indebted by reponses. thanks again.day by day i am getting sql with gretater interest

[Updated on: Thu, 13 September 2007 12:23] by Moderator

Report message to a moderator

Re: help [message #267564 is a reply to message #267509] Thu, 13 September 2007 13:09 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
select ename,sal from emp
where sal in
(select losal from salgrade where rownum<=2)
order by sal;


You want to order by sal, correct?
Re: help [message #267573 is a reply to message #267564] Thu, 13 September 2007 13:43 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You want to order by sal, correct?

No he wants only the employees with the 2 first salaries.

Regards
Michel
Previous Topic: simple in,between clause doubt
Next Topic: OCP Exam 1Z0-040: Installation (merged)
Goto Forum:
  


Current Time: Sun Dec 11 04:23:51 CST 2016

Total time taken to generate the page: 0.06010 seconds