CODEWITHSUNDEEP
sqloracle-database
AHMER93
AHMER93

Reputation: 35

creating a view SQL, always getting error

CREATE VIEW CARAVAN AS
    SELECT ANNUAL_RENT, BOOKING_FEE
    FROM CARAVAN
    WHERE ANNUAL_RENT < 3000

this is my failed sql. the error is

ORA-00928: missing SELECT keyword

Upvotes: 2

Views: 1274

Answers (1)

spencer7593
spencer7593

Reputation: 108370

Try running just the SELECT statement:

SELECT ANNUAL_RENT, BOOKING_FEE
FROM CARAVAN
WHERE ANNUAL_RENT < 3000

And see if that returns a result, or if it generates an error.

My suspicion is that CARAVAN is a view that is INVALID. You can check that with a query from a dictionary view, e.g.

SELECT *
  FROM dba_objects
 WHERE object_name = 'CARAVAN'

If you don't have privileges on dba_objects, then reference the all_objects instead.

The referenced CARAVAN object is either an object in your schema, a public synonym, or its an invalid reference.

As Alex Poole mentioned, it's possible to create a view that has invalid syntax, by using a CREATE FORCE VIEW statement. (I suspect that the referenced object CARAVAN is a view that contains invalid syntax.)

Identifiers in Oracle have to be unique within a schema. It's very strange that you would be creating a view named CARAVAN that references an object already named CARAVAN. It's not clear what problem you are trying to solve with this particular CREATE VIEW statement.

Upvotes: 3

Related Questions