CODEWITHSUNDEEP
sqloracle-databasesql-view
Anna K
Anna K

Reputation: 1774

ORA-00998: must name this expression with a column alias. In a different schema it works

I'm porting application in oracle and I have there a working view code:

CREATE OR REPLACE FORCE VIEW "MyNewSchema.SPECIAL_APPROVERS_VW" ("NAME", "ID", "PERSON_NUMBER", "USER_NAME", "BUSINESS_UNIT_ID", "BUSINESS_UNIT_NAME", "ROW_TYPE") AS
select e.FULL_NAME, e.PERSON_ID, e.PERSON_NUMBER, null, e.ORGANIZATION_ID, d.NAME, 'E'
  from MyNewSchema.EMPLOYEES_IMPORT e
  join MyNewSchema.DEPARTMENTS_IMPORT d on d.ORGANIZATION_ID = e.ORGANIZATION_ID
union all
select d.NAME, d.ORGANIZATION_ID, DEPARTMENT_ID, null, null, null, 'D'
from MyNewSchema.DEPARTMENTS_IMPORT d

Why it works in another schema but when I migrated tables to MyNewSchema it doesn't work and I'm getting a such error:

Compilation failed,line 0 (10:36:04)
ORA-00998: must name this expression with a column alias

Upvotes: 0

Views: 477

Answers (1)

VBoka
VBoka

Reputation: 9083

I think this will work:

CREATE OR REPLACE FORCE VIEW SPECIAL_APPROVERS_VW ("NAME", "ID", "PERSON_NUMBER", "USER_NAME", "BUSINESS_UNIT_ID", "BUSINESS_UNIT_NAME", "ROW_TYPE") AS
select e.FULL_NAME, e.PERSON_ID, e.PERSON_NUMBER, null, e.ORGANIZATION_ID, d.NAME, 'E'
  from EMPLOYEES_IMPORT e
  join DEPARTMENTS_IMPORT d on d.ORGANIZATION_ID = e.ORGANIZATION_ID
union all
select d.NAME, d.ORGANIZATION_ID, DEPARTMENT_ID, null, null, null, 'D'
from DEPARTMENTS_IMPORT d

As you have mentioned you have now all the tables in this schema and you are creating a view in that schema...

Upvotes: 1

Related Questions