Why SQL error ORA-00907: missing right parenthesis

Question

Here is my query:

SELECT person_name
  FROM travel_card
 WHERE id IN
       (SELECT travel_card_id
          FROM travel_payment
         WHERE entry_station_id IN
               (SELECT id
                  FROM station
                 WHERE name = 'Marina Bay MRT Station'
                    OR exit_station_id IN
                       (SELECT id
                          FROM station
                         WHERE name = 'Marina Bay MRT Station'))) I
 ORDER BY travel_card.person_name

Why do I get this error?

SQL ERROR: from station where name='Marina Bay MRT Station')I)
ERROR at line 4: ORA-00907: missing right parenthesis

Answer 1

Formatting your code is the first step to being able to read it and find bugs, and it's clear there are some bugs.

I think this is what you meant to write:

select person_name
from travel_card
where id in (
    select travel_card_id
    from travel_payment
    where entry_station_id = (
        select id
        from station
        where name = 'Marina Bay MRT Station')
    or exit_station_id = (
        select id
        from station
        where name = 'Marina Bay MRT Station')
)
order by travel_card.person_name

Notice that = replaced in for the station lookups and I un-nested the subqueries.

You could (and should) express this using only joins, but I left the structure as similar to your query so the differences were minimised.

Answer 2

A subquery following an IN operator cannot be aliased, otherwise you'd get

• ORA-00907 if the alias is nested in parentheses -> ))I)

• ORA-00933 if the alias follows all of the parentheses -> )))I

considering your current case.

Seems that need to tidy up the query and presumably you want to write it as the following :

SELECT person_name
  FROM travel_card
 WHERE id IN
       (SELECT travel_card_id
          FROM travel_payment tp
          JOIN station s
            ON s.id IN (tp.entry_station_id,tp.exit_station_id)
         WHERE s.name = 'Marina Bay MRT Station')
 ORDER BY person_name; 

yet, might be shorter :

SELECT DISTINCT tc.person_name
  FROM travel_payment tp
  JOIN station s
    ON s.id IN (tp.entry_station_id, tp.exit_station_id)
  JOIN travel_card tc
    ON tc.person_name = tp.travel_card_id
 WHERE s.name = 'Marina Bay MRT Station'
 ORDER BY tc.person_name