Why SQL error ORA-00907: missing right parenthesis

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.

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