Sokrates on Oracle

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

:-)

About these ads

One Response to “Workaround for “ORA-00998: must name this expression with a column alias””

  1. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: