CODEWITHSUNDEEP
sqloracle-database
Ankit
Ankit

Reputation: 433

SQL Query Error in Oracle

On Exeuting Following Query:

SELECT ID
  FROM INSTITUTE
 WHERE MEMBER_ID IN (
    SELECT ID 
      FROM MEMBER 
     WHERE ID IN (765,769,753,774,778,779,781,790,799,809,
                  820,823,855,835,839,842,845,849,850,851)
    ORDER BY NAME ASC
)

I am getting following error:

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 5 Column: 81

How can I troubleshoot this bug???

Upvotes: 0

Views: 133

Answers (7)

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 121067

Your ORDER BY in the sub-select is useless. Consider joining the two tables instead, like this:

SELECT ID
FROM INSTITUTE i, MEMBER m
WHERE
i.MEMBER_ID = m.ID
AND i.MEMBER_ID IN
    (
        765,769,753,774,778,779,781,790,799,809,820,823,855,835,839,842,845,849,850,851
    )
ORDER BY m.NAME

Upvotes: 1

Ollie
Ollie

Reputation: 17578

Your ORDER BY is inside your subselect and therefore useless to the final order of the results but is causing your error.

Move it outside the subselect.

SELECT id
  FROM institute
 WHERE member_id IN
          (  SELECT id
               FROM MEMBER
              WHERE id IN ( 765, 769, 753, 774, 778, 779, 781, 790,
                            799, 809, 820, 823, 855, 835, 839, 842,
                            845, 849, 850, 851 )
          )
 ORDER BY name ASC

N.B.: I can only assume that name is a column in the institute table as otherwise the ORDER BY would be totally redundant. Therefore it should be ordering the main SELECT.

or remove it entirely...

SELECT id
  FROM institute
 WHERE member_id IN
          (  SELECT id
               FROM MEMBER
              WHERE id IN ( 765, 769, 753, 774, 778, 779, 781, 790,
                            799, 809, 820, 823, 855, 835, 839, 842,
                            845, 849, 850, 851 )
          )

Wouldn't this query be more efficient though?

SELECT i.id
  FROM institute i
 INNER JOIN member m
    ON (i.member_id = m.id)
 WHERE m.id IN ( 765, 769, 753, 774, 778, 779, 781, 790,
                 799, 809, 820, 823, 855, 835, 839, 842,
                 845, 849, 850, 851 )

Upvotes: 3

AndréB
AndréB

Reputation: 65

The order by in the subquery is completely useless, it won't order the output at all. Remove it and it will work.

Btw, moving it outside won't give the expected result, except institute.name has the same values as member.name ;-)

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270775

This is likely because your ORDER BY clause occurs inside the outer MEMBER_ID IN(...) clause.

This query looks suspiciously as though it should be accomplished with a JOIN instead of two IN() clauses:

SELECT INSTITUTE.id
FROM 
  INSTITUTE JOIN MEMBER ON INSTITUTE.MEMBER_ID = MEMBER.ID
WHERE MEMBER.ID IN IN (765,769,753,774,778,779,781,790,799,809,820,823,855,835,839,842,845,849,850,851)
ORDER BY MEMBER.NAME ASC

Upvotes: 0

diagonalbatman
diagonalbatman

Reputation: 18012

SELECT ID FROM INSTITUTE 
WHERE MEMBER_ID IN ( SELECT ID FROM MEMBER WHERE ID IN
( 765,769,753,774,778,779,781,790,799,809,820,823,855,835,839,842,845,849,850,851 )) ORDER BY NAME ASC;

Try this - your order by is in the wrong place.

Upvotes: 0

ipr101
ipr101

Reputation: 24236

Try this - 

SELECT ID 
FROM INSTITUTE 
WHERE MEMBER_ID IN (
SELECT ID FROM MEMBER WHERE ID IN (765,769,753,774,778,779,781,790,799,809,820,823,855,835,839,842,845,849,850,851))
ORDER BY NAME ASC

I suspect you need the ORDER BY statement outside of the IN clause

Upvotes: 0

bpgergo
bpgergo

Reputation: 16057

order by should bu put out of the IN clause

SELECT ID
FROM INSTITUTE
WHERE MEMBER_ID IN (
SELECT ID FROM MEMBER WHERE ID IN (
765,769,753,774,778,779,781,790,799,809,820,823,855,835,839,842,845,849,850,851 )
)
ORDER BY NAME ASC

Upvotes: 0

Related Questions