SQL Query Error in Oracle

Question

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???

Why are you nesting so many subselects? The issue is the ORDER BY in the second subselect. — diagonalbatman
– diagonalbatman, Commented Nov 9, 2011 at 13:56

Ollie · Accepted Answer · 2011-11-09 14:02:15Z

3

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 )

edited Nov 9, 2011 at 14:02

answered Nov 9, 2011 at 13:55

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Klaus Byskov Pedersen Over a year ago

You are now ordering results by institute.name (if it exists), and I'm quite convinced that the OP wants the results order by member.name. Why not do a join on institute.member_id = member.id and remove the sub-select entirely?

diagonalbatman Over a year ago

@KlausByskovHoffmann it is possible - however, the OP never stated their intended use / output, so it would be a guess. But i agree that would be logical.

Ollie Over a year ago

In trying to second guess the OP's table structure, not all answers are going to be perfect, it is just an approximation of what he is trying to achieve whilst removing the issue that has caused them to ask the question.

Klaus Byskov Pedersen · Accepted Answer · 2011-11-09 14:03:25Z

1

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

answered Nov 9, 2011 at 14:03

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Comments

bpgergo · Accepted Answer · 2011-11-09 13:56:57Z

0

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

answered Nov 9, 2011 at 13:56

bpgergo

16.1k5 gold badges48 silver badges70 bronze badges

1 Comment

Klaus Byskov Pedersen Over a year ago

Everybody here seems to think that Institute has a name column?

ipr101 · Accepted Answer · 2011-11-09 13:57:03Z

0

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

answered Nov 9, 2011 at 13:57

ipr101

24.2k8 gold badges61 silver badges62 bronze badges

1 Comment

Klaus Byskov Pedersen Over a year ago

Everybody here seems to think that Institute has a name column?

diagonalbatman · Accepted Answer · 2011-11-09 13:57:26Z

0

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.

answered Nov 9, 2011 at 13:57

diagonalbatman

18.1k3 gold badges33 silver badges31 bronze badges

2 Comments

Klaus Byskov Pedersen Over a year ago

Everybody here seems to think that Institute has a name column?

diagonalbatman Over a year ago

I thought this when writing the answer - but thought the best way to illustrate where an ORDER BY clause should be would be to illustrate by moving "NAME" outside to the main query. However i accept that there was no information supporting this decision.

Michael Berkowski · Accepted Answer · 2011-11-09 13:59:10Z

0

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

answered Nov 9, 2011 at 13:59

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Comments

AndréB · Accepted Answer · 2011-11-09 14:00:26Z

0

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 ;-)

answered Nov 9, 2011 at 14:00

AndréB

658 bronze badges

Collectives™ on Stack Overflow

SQL Query Error in Oracle

7 Answers 7

3 Comments

Comments

1 Comment

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

Comments

1 Comment

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related