SQL command not properly ended after the as statement

Question

I want to create a view named PROJECT_FEMALE. From 3 different tables but sql gives me that error.

CREATE VIEW PROJECT_FEMALE 
AS
    SELECT e.Ssn, e.Fname, e.Lname, p.Pnumber, p.Pname, w.Hours
    FROM Employee AS e, Works_on AS w, Project AS p
    WHERE e.Ssn = w.Essn AND w.pno = p.Pnumber AND e.sex = 'F';

Error:

[Code: 933, SQL State: 42000] ORA-00933: SQL command not properly ended
[Script position: 103 - 105]

It points as statement after the Employee but I do not understand what is the problem and how can I fix it?

Learn to use proper, explicit, **standard**, readable `JOIN` syntax. — Gordon Linoff, Nov 27 '20 at 16:08
Yeah, when I do that with 'Join' it works fine I think it is from syntax . Thanks — Leo S, Nov 27 '20 at 16:16

score 1 · Accepted Answer · answered Nov 27 '20 at 16:18

Oracle does not support table aliases with AS, you can only use AS for column aliases; so, your code should be:

CREATE VIEW PROJECT_FEMALE
AS
   SELECT e.Ssn,
          e.Fname,
          e.Lname,
          p.Pnumber,
          p.Pname,
          w.Hours
     FROM Employeee, Works_on w, Project p
    WHERE e.Ssn = w.Essn AND w.pno = p.Pnumber AND e.sex = 'F';

or (better)

CREATE VIEW PROJECT_FEMALE
AS
   SELECT e.Ssn,
          e.Fname,
          e.Lname,
          p.Pnumber,
          p.Pname,
          w.Hours
     FROM Employee e
          INNER JOIN Works_on w ON (e.Ssn = w.Essn)
          INNER JOIN Project p ON (w.pno = p.Pnumber)
    WHERE e.sex = 'F';

SQL command not properly ended after the as statement

1 Answers1