Oracle Throwing SQL Error when creating a View

Question

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.

Quassnoi · Accepted Answer · 2009-04-08 16:58:48Z

4

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

answered Apr 8, 2009 at 16:58

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Justin Cave · Accepted Answer · 2009-04-08 17:03:52Z

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

tpdi · Accepted Answer · 2009-04-08 17:00:57Z

3

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

answered Apr 8, 2009 at 17:00

tpdi

35.3k11 gold badges83 silver badges123 bronze badges

1 Comment

Quassnoi Over a year ago

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.

JosephStyons · Accepted Answer · 2009-04-08 17:14:00Z

2

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'

answered Apr 8, 2009 at 17:14

JosephStyons

59.1k64 gold badges169 silver badges240 bronze badges

Collectives™ on Stack Overflow

Oracle Throwing SQL Error when creating a View

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related