Workaround for “ORA-00998: must name this expression with a column alias”
Posted by Matthias Rogel on 27. January 2012
Did you ever encounter
ORA-00998: must name this expression with a column alias
and wondered why ?
Doc says
Error: ORA 998 Text: must name this expression with a column alias ------------------------------------------------------------------------------- Cause: An expression or function was used in a CREATE VIEW statement, but no corresponding column name was specified. When expressions or functions are used in a view, all column names for the view must be explicitly specified in the CREATE VIEW statement. Action: Enter a column name for each column in the view in parentheses after the view name.
but that is (at most) only half the truth.
Here is my story about ORA-00998:
sokrates@11.2 > create table temp as select count(*) from dual;
create table temp as select count(*) from dual
*
ERROR at line 1:
ORA-00998: must name this expression with a column alias
sokrates@11.2 > REM must ?
sokrates@11.2 > REM I don't think so ...
sokrates@11.2 > REM let's see ...
sokrates@11.2 > create table temp as select * from (select count(*) from dual);
Table created.
sokrates@11.2 > desc temp
Name Null? Type
----------------------------------------- -------- ----------------------------
COUNT(*) NUMBER
sokrates@11.2 > REM hehe !
sokrates@11.2 > select "COUNT(*)" from temp;
COUNT(*)
----------
1
sokrates@11.2 > CREATE VIEW test_v AS
2 SELECT owner || '.' || table_name
3 FROM all_tables
4 WHERE owner = USER;
SELECT owner || '.' || table_name
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
sokrates@11.2 > create view test_v as
2 select * from (
3 SELECT owner || '.' || table_name
4 FROM all_tables
5 WHERE owner = USER
6 );
View created.
sokrates@11.2 > desc test_v
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER||'.'||TABLE_NAME VARCHAR2(61)
sokrates@11.2 > select "OWNER||'.'||TABLE_NAME" from test_v where rownum=1;
OWNER||'.'||TABLE_NAME
-------------------------------------------------------------
SOKRATES.T
Miguel Moreno said
Mr. Rogel:
You are correct in stating that the documentation only says half of the story… Recently I was getting this error ( ORA-00998 ) when creating a temporary work table, and after some troubleshooting I realized that not only functions and expressions must have a column alias, but pseudocolumns also must have a column alias:
Example:
create table my_table as select a.rowid, a.dummy from dual a;
> Error at Command Line:1 Column:32
> SQL Error: ORA-00998: must name this expression with a column alias
create table my_table as select a.rowid as the_rowid, a.dummy from dual a;
> table MY_TABLE created.
In a fairly complex query it took me sometime to figure it out.