Wednesday, October 17, 2007

ORA-00998: must name this expression with a column alias

I was attempting to quickly create a view containing some expressions when i got the following error message:
ORA-00998: must name this expression with a column alias
This error is typical of a situation where you are attempting to create a view without specifying the column names after the view name, but you are relying on the column name or the column alias specified in the query, as in this example:
CREATE VIEW test_v AS
SELECT owner || '.' || table_name
FROM all_tables
WHERE owner = USER;

ORA-00998: must name this expression with a column alias
The error is easily fixed as follows:

CREATE OR REPLACE VIEW test_v AS
SELECT owner || '.' || table_name AS qualified_table_name
FROM all_tables
WHERE owner = USER;
or in this other equivalent way:
CREATE OR REPLACE VIEW test_v (qualified_table_name)
AS
SELECT owner || '.' || table_name
FROM all_tables
WHERE owner = USER;
If the view is based mostly on expressions the first approach could be faster or less error prone because you can control the association between the expression and the column name more directly (or so it looks to me...).


See message translations for ORA-00998 and search additional resources.



ORA-00998: fornire un nome a questa espressione con uno pseudonimo di colonna
ORA-00998: debe proporcionar un nombre a esta expresión con un alias de columna
ORA-00998: s'ha d'anomenar aquesta expressió amb un àlies de columna
ORA-00998: il faut nommer cette expression avec un libellé de colonne
ORA-00998: Dieser Ausdruck braucht einen Spalten-Alias
ORA-00998: πρέπει να ονομάσετε αυτή την έκφραση με ψευδώνυμο στήλης
ORA-00998: dette udtryk skal navngives med et kolonnealias
ORA-00998: uttrycket måste ges ett kolumnalias
ORA-00998: dette uttrykket må navngis med et kolonnealias
ORA-00998: tälle lausekkeelle on annettava sarakealias
ORA-00998: a kifejezéshez meg kell adni egy oszlop átnevezést
ORA-00998: această expresie trebuie numită cu un alias de coloană
ORA-00998: Deze uitdrukking moet een kolomalias hebben.
ORA-00998: esta expressão deve ser nomeada com um apelido de coluna
ORA-00998: tem que dar um nome a esta expressão com um pseudónimo de coluna
ORA-00998: Вы должны присвоить имя этому выражению, задав псевдоним столбца
ORA-00998: tento výraz je třeba pojmenovat alternativním jménem sloupce
ORA-00998: tento výraz musí mať stĺpcový alias
ORA-00998: wyrażenie to musi być nazwane z użyciem aliasu kolumny
ORA-00998: bu ifade bir sütun diğer adı ile adlandırılmalıdır
ORA-00998: must name this expression with a column alias

2 comments:

Sokrates said...

Hi Flavio,

create view test_v as
select * from (
SELECT owner || '.' || table_name
FROM all_tables
WHERE owner = USER
);

hehe
http://marogel.wordpress.com/2012/01/27/workaround-for-ora-00998-must-name-this-expression-with-a-column-alias/

Byte64 said...

Hi Sokrates,
the good news is in that if you exceed the 30 character limit for the column name, you'll get ORA-01948, so you cannot create a column containing more than 30 characters of junk ;-)

Cheers
Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles