tags:

views:

238

answers:

4

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.

+4  A: 
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

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

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

tpdi
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.
Quassnoi
+3  A: 

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
Justin Cave
+1, nice touch with the sqlplus hint...
DCookie
+2  A: 

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'
JosephStyons