SQL CREATE VIEW Error

Question

I just wonder why my View is not working(ORA-00905: missing keyword). Thanks for help

CREATE OR REPLACE VIEW Tickets_RAFAL
AS
SELECT t.ticket_id,
 t.subject,
 t.descr description,
 t.assigned_to,
 t.created_on,
 t.created_by,
 t.closed_on,
 td.number_of_details
FROM tickets t join (select ticket_id,count(ticket_id) number_of_details FROM Ticket_Details 
GROUP  BY ticket_id) as td
on t.ticket_id=td.ticket_id

This is select statement, where is the view? – Kenan Zahirovic Dec 31 '14 at 09:13 — Kenan Zahirovic, Dec 31 '14 at 09:13

score 2 · Accepted Answer · answered Dec 31 '14 at 09:13

2

Oracle does not support using AS for a (derived) table alias. You need to remove it:

.....
FROM tickets t 
   join (select ticket_id,count(ticket_id) number_of_details 
         FROM Ticket_Details 
         GROUP BY ticket_id
   ) td --< no AS here
   on t.ticket_id=td.ticket_id

answered Dec 31 '14 at 09:13

Thanks a lot ! that was the issue :) – rafalbballer Dec 31 '14 at 09:23

score 0 · Answer 2 · answered Dec 31 '14 at 09:23

If you use SQL*Plus, it would be so easy to find and fix the error. And ideally I would expect ORA-00933: SQL command not properly ended and not ORA-00905: missing keyword.

For example,

SQL> SELECT COUNT(*) FROM emp e,
  2    (SELECT * FROM emp
  3    ) as "A" WHERE e.empno = A.empno
  4  /
  ) as "A" WHERE e.empno = A.empno
    *
ERROR at line 3:
ORA-00933: SQL command not properly ended


SQL>

The error stack clearly shows the error is at line 3 due to the incorrect syntax of the alias using keyword AS.

Your modified create view would be -

CREATE OR REPLACE VIEW Tickets_RAFAL
AS
  SELECT t.ticket_id,
    t.subject,
    t.descr description,
    t.assigned_to,
    t.created_on,
    t.created_by,
    t.closed_on,
    td.number_of_details
  FROM tickets t
  JOIN
    (SELECT ticket_id,
      COUNT(ticket_id) number_of_details
    FROM Ticket_Details
    GROUP BY ticket_id
    ) td
  ON t.ticket_id=td.ticket_id
/

Unfortunatelly i am using ORACLE APEX build in query editor and i am not able to install anything on my PC. — rafalbballer, Dec 31 '14 at 09:37

SQL CREATE VIEW Error

2 Answers2