ORA-00918:column ambiguously defined on inner join

1
votes

Keep getting an error when trying to create a view with an inner join:

    CREATE OR REPLACE VIEW lab3view3
AS
SELECT 
       f.firstname,
       f.lastname,
       s.firstname,
       s.lastname,
       c.coursename, 
       sec.days, 
       TO_CHAR (sec.starttime, 'MM/DD/YYYY') AS startdate,
       TO_CHAR(sec.starttime, 'HH:MI AM    ') AS starttime,
       TO_CHAR(sec.endtime, 'MM/DD/YYYY') AS enddate, 
       TO_CHAR(sec.endtime, 'HH:MI AM    ') AS endtime,
       sec.sectionid,
       l.building, 
       l.room,
       g.grade
FROM  section sec
        INNER JOIN grade g
                    ON g.sectionid = sec.sectionid
        INNER JOIN course c
                    ON c.courseid = sec.courseid
        INNER JOIN location l
                    ON l.locationid = sec.locationid
        INNER JOIN student s
                    ON s.studentid = g.studentid
        INNER JOIN instructor i
                    ON i.sectionid = sec.sectionid
        INNER JOIN faculty f
                    ON f.facultyid = i.facultyid
        INNER JOIN section sec
                    ON sec.sectionid = g.sectionid;

Keep getting this error:

ERROR AT LINE 1;

ORA-00918:column ambiguously defined

Any ideas on how to fix this?

2
oracle

2 Answers

2
votes

You need to also prefix the times in the TO_CHAR (starttime and endtime) call with the correct table. Also you are selecting firstname and lastname twice from different tables, the second one will be renamed to firstname_1 then, they have to be renamed because you are creating a view, see @sstan's answer.

1
votes

@maraca's answer is correct. But once you fix the ambiguously defined column error, you will then get

ORA-00957: duplicate column name

When creating a view, every selected column needs to have a unique name. Make sure you alias the duplicate column names in the select clause accordingly.

Example:

CREATE OR REPLACE VIEW lab3view3
    AS
    SELECT faculty.firstname as faculty_firstname,
           faculty.lastname as faculty_lastname,
           student.firstname as student_firstname,
           student.lastname as student_lastname,
...