Select statement throws "ORA-00907: missing right parenthesis"

Question

I am a little new to Oracle, and can't seem to run this query below.

SELECT user_key AS oldKey,
       (SELECT 
            user_key
        FROM
            user_mapping AS u
        WHERE lower(u.username) = u.lower_username
          AND lower(u.username) = lower(um.username)
        ) AS newKey,
       um.username,
       um.lower_username
  FROM user_mapping AS um
  WHERE user_key IN (SELECT user_key
                      FROM user_mapping
                      WHERE (lower(username) != lower_username))
                         OR (lower_username is null)

It throws the "ORA-00907: missing right parenthesis" error, but I don't see any syntax error, and the query works fine when I run it against a MySQL database.

Checking some other issues related to the missing parenthesis, but it's mostly about not explicitly stating the primary key, or specifying something as not null, but I don't think that seems to be the issue here as well.

Do let me know if I am missing or am wrong in anything above.

Thank you.

Remove the `as` from the table aliases in the from clauses. Might be other problems. — Mat, Aug 06 '18 at 08:27
Possible duplicate of [How to use the 'as' keyword to alias a table in Oracle?](https://stackoverflow.com/questions/21145028/how-to-use-the-as-keyword-to-alias-a-table-in-oracle) — Alex Poole, Aug 06 '18 at 08:32
one parantesis ist to much `lower_username))` and one to few on the end of you query — hotfix, Aug 06 '18 at 08:33

score 1 · Answer 1 · answered Aug 06 '18 at 21:32

You have an extra ) in line 14 and you were missing a ) at the end (i.e. after line 15).

I've fix both issues. Try the following

SELECT user_key AS oldKey,
       (SELECT 
            user_key
        FROM
            user_mapping AS u
        WHERE lower(u.username) = u.lower_username
          AND lower(u.username) = lower(um.username)
        ) AS newKey,
       um.username,
       um.lower_username
  FROM user_mapping AS um
  WHERE user_key IN ( SELECT user_key
                      FROM user_mapping
                      WHERE (lower(username) != lower_username)
                         OR (lower_username is null)
                    )

Select statement throws "ORA-00907: missing right parenthesis"

1 Answers1