Search code examples
sqloraclesql-view

ora-00904 invalid identifier on creating a view

I am having issues with creating a view , the sql query tests successfully from the create view pane as seen on the right pane

enter image description here

Oracle is throwing me an invalid identifier , but oracle does not point to which part of the query

enter image description here

Solution

  • Your example can be simplified to:

    CREATE VIEW test AS
  SELECT (SELECT 2 FROM DUAL) - (SELECT 1 FROM DUAL)
  FROM DUAL;

    Which fails as Oracle does not know what to call the column the view generates and SQL Developer is trying to automatically generate the SQL by copying the default alias into the signature:

    CREATE VIEW test( (SELECT2FROMDUAL)-(SELECT1FROMDUAL) ) AS
  SELECT (SELECT 2 FROM DUAL) - (SELECT 1 FROM DUAL)
  FROM DUAL;

    Which is an invalid identifier.

    Either set the identifier for the column in the query:

    CREATE VIEW test AS
  SELECT (SELECT 2 FROM DUAL) - (SELECT 1 FROM DUAL) AS value
  FROM DUAL;

    or in the signature:

    CREATE VIEW test(value) AS
  SELECT (SELECT 2 FROM DUAL) - (SELECT 1 FROM DUAL)
  FROM DUAL;

    fiddle