SQL Error: ORA-00918: Column ambiguous

Question

Please help me find out what I'm doing wrong here:

Error starting with line: 1 in command -

CREATE VIEW VW_ROUTE AS
  SELECT ROUTE_NAME
       , FARE
       , DESTINATION
       , ROUTE_ID
       , STOP_NAME
       , TERMINUS
       , NUMBER_OF_STOPS
  FROM STOPS S
    LEFT JOIN ROUTE R
        ON S.ROUTE_ID = R.ROUTE_ID
 WHERE NUMBER_OF_STOPS > ('1')

Error at Command Line : 5 Column : 10
Error report -
SQL Error: ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
*Cause:    
*Action:

Answer 1

You have the same column name in both tables. Add aliases.

CREATE VIEW VW_ROUTE AS
SELECT ROUTE_NAME
       , FARE
       , DESTINATION
       , S.ROUTE_ID          -- ROUTE_ID exists in table STOPS/ROUTE
       , STOP_NAME
       , TERMINUS
       , NUMBER_OF_STOPS
FROM STOPS S
LEFT JOIN ROUTE R
  ON S.ROUTE_ID = R.ROUTE_ID
WHERE NUMBER_OF_STOPS > ('1');

It is good practice to use aliases in all cases to avoid ambiguity.