Oracle Throwing SQL Error when creating a View

Hi all,

I'm trying to create a view in an Oracle database, but keep getting an ORA-00907 error (missing right parenthesis). My SQL is as below:

CREATE VIEW my_view AS 
(
SELECT metadata.ID,metadata.Field1,metadata.Field2,metadata.Field3,metadata.Field4,attribute1.StrValue AS Attr1, attribute2.StrValue AS Attr2 
FROM metadata,data AS attribute1,data AS attribute2 
WHERE 
 (
 metadata.Type = 'TYPE1'
 ) 
AND 
 (
 metadata.ID = attribute1.ID AND attribute1.name = 'attr1'
 ) 
 AND 
 (
 metadata.ID = attribute2.ID AND attribute2.name = 'attr2'
 )
)

Where the table metadata defines entities, and data defines attributes for those entities.

This works fine in MS SQL and MySQL, but I keep getting the above error from Oracle.

Not been working with Oracle too long, so I don't know a whole lot about its quirks.

From stackoverflow

• CREATE VIEW my_view AS 
  (
  SELECT  metadata.ID,metadata.Field1,metadata.Field2,
          metadata.Field3,metadata.Field4,
          attribute1.StrValue AS Attr1, attribute2.StrValue AS Attr2 
  FROM    metadata, data /* No AS here */ attribute1,
          data /* No AS here */ attribute2 
  WHERE 
          (
          metadata.Type = 'TYPE1'
          ) 
  AND 
          (
          metadata.ID = attribute1.ID AND attribute1.name = 'attr1'
          ) 
          AND 
          (
          metadata.ID = attribute2.ID AND attribute2.name = 'attr2'
          )
  )
  

  I removed AS between data and attribute1

  DCookie : +1, your answers are always clear and on point

• Since none of your parentheses are necessary, and they make reading the code harder, why not get rid of them?

  Quassnoi : It's not the parenteses, it's the nice Oracle syntax parser's way to report errors. Oracle does not recognize AS between table name and alias.

• You need to remove the AS in the FROM clause. Oracle allows the optional AS for aliasing column names but not for providing aliases

  SQL> ed
  Wrote file afiedt.buf
  
    1  CREATE VIEW my_view AS
    2  (
    3  SELECT metadata.ID,metadata.Field1,metadata.Field2,metadata.Field3,metadata
  .Field4,attribute1.StrValue AS Attr1, attribute2.StrValue AS Attr2
    4  FROM metadata,data attribute1,data attribute2
    5  WHERE
    6          (
    7          metadata.Type = 'TYPE1'
    8          )
    9  AND
   10          (
   11          metadata.ID = attribute1.ID AND attribute1.name = 'attr1'
   12          )
   13          AND
   14          (
   15          metadata.ID = attribute2.ID AND attribute2.name = 'attr2'
   16          )
   17* )
  SQL> /
  
  View created.
  

  Depending on the tool you're using, it may be useful to know that SQL*Plus will show you exactly where a syntax error is occurring-- the snippet below shows it objecting to the AS keyword.

  SQL> ed
  Wrote file afiedt.buf
  
    1  CREATE VIEW my_view AS
    2  (
    3  SELECT metadata.ID,metadata.Field1,metadata.Field2,metadata.Field3,metadata
  .Field4,attribute1.StrValue AS Attr1, attribute2.StrValue AS Attr2
    4  FROM metadata,data AS attribute1,data AS attribute2
    5  WHERE
    6          (
    7          metadata.Type = 'TYPE1'
    8          )
    9  AND
   10          (
   11          metadata.ID = attribute1.ID AND attribute1.name = 'attr1'
   12          )
   13          AND
   14          (
   15          metadata.ID = attribute2.ID AND attribute2.name = 'attr2'
   16          )
   17* )
  SQL> /
  FROM metadata,data AS attribute1,data AS attribute2
                     *
  ERROR at line 4:
  ORA-00907: missing right parenthesis
  

  DCookie : +1, nice touch with the sqlplus hint...

• Others have pointed out the AS keyword in your FROM clause as the problem. I suggest the following as a cleaner, easier to read version of the same statement:

  create view my_view as 
  select
    meta.id
   ,meta.field1
   ,meta.field2
   ,meta.field3
   ,meta.field4
   ,att1.strvalue as attr1
   ,att2.strvalue as attr2 
  from
    metadata meta
   ,data att1
   ,data att2 
  where meta.id = att1.id 
    and meta.id = att2.id 
    and meta.type = 'TYPE1'
    and att1.name = 'attr1'
    and att2.name = 'attr2'