FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / ORA - Oracle Database Error Codes - Search via specific error codes. / ORA-00000 Through ORA-09989 / ORA-00998: must name this expression with a column alias / Latest Posts

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

Admin — Tue, 15 Jun 2010 09:44:12 GMT

Oracle Error :: ORA-00998

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.

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

Admin — Tue, 15 Jun 2010 09:43:48 GMT

Try this...

create table
mebs_temp10 tablespace mebs_tempt nologging as
SELECT
TRUNC(meddate) as meddate,
ctype,
btext btext,
COUNT (1) count_field,
sum(duration/60) sum_duration,
SUM (bamount) sum_bamount
FROM mells.hcalls WHERE meddate >= '01-JAN-2009' and meddate < ' GROUP BY meddate, ctype, btext

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

Admin — Tue, 15 Jun 2010 09:43:35 GMT

create table mebs_temp10 tablespace mebs_tempt nologging as SELECT TRUNC(meddate) as meddate, ctype , btext btext, COUNT (1), sum(duration/60), SUM (bamount) FROM mells.hcalls WHERE meddate >= '01-JAN-2009' and meddate < ' GROUP BY meddate, ctype, btext
*
ERROR at line 1:
ORA-00998: must name this expression with a column alias

I tried giving a alias for the columns ctype , btext .. but helpless. which column to be aliased ?

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

Admin — Tue, 15 Jun 2010 09:42:51 GMT

CREATE OR REPLACE VIEW PARTNER_SUMMARY_SEEMORE_VW
AS
SELECT PARTNER,
TO_CHAR (SUM(TOTAL)) as TOTAL,
TO_CHAR (SUM(ORG_SENT)) as ORG_SENT,
TO_CHAR (SUM(ORG_RECEIVED)) as ORG_RECEIVED,
TO_CHAR (SUM(TAX_RTN_PROCESSED)) as TAX_RTN_PROCESSED,
TO_CHAR (SUM(TAX_RTN_REVIEWED)) as TAX_RTN_REVIEWED,
TO_CHAR (SUM(TAX_RTN_SENT_TO_TAXPAYER)) as TAX_RTN_SENT_TO_TAXPAYER,
office_name as office_name,
TO_CHAR(NULL) some_head
FROM PARTNER_SUMMARY_VW
GROUP BY partner,office_name
UNION ALL
SELECT total_text,
force_bold_fn(a) as a,
force_bold_fn(TOTAL_ORG_SENT) as TOTAL_ORG_SENT,
force_bold_fn(TOTAL_ORG_RECEIVED) as TOTAL_ORG_RECEIVED,
force_bold_fn(TOTAL_ORG_PROCESS) as TOTAL_ORG_PROCESS,
force_bold_fn(TOTAL_ORG_REVIEW) as TOTAL_ORG_REVIEW,
force_bold_fn(TOTAL_ORG_CLIENT) as TOTAL_ORG_CLIENT,
TO_CHAR(NULL),
force_bold_fn(TOTAL_ORG_PARTNER) as TOTAL_ORG_PARTNER
FROM gts_totals_vw

you need to give a column header as I gave.. replace it with your name..

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

Admin — Tue, 15 Jun 2010 09:42:34 GMT

Getting following ora error code :

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

when trying to create this query

CREATE OR REPLACE VIEW PARTNER_SUMMARY_SEEMORE_VW
AS
SELECT PARTNER,
TO_CHAR (SUM(TOTAL)) as TOTAL,
TO_CHAR (SUM(ORG_SENT)) as ORG_SENT,
TO_CHAR (SUM(ORG_RECEIVED)) as ORG_RECEIVED,
TO_CHAR (SUM(TAX_RTN_PROCESSED)) as TAX_RTN_PROCESSED,
TO_CHAR (SUM(TAX_RTN_REVIEWED)) as TAX_RTN_REVIEWED,
TO_CHAR (SUM(TAX_RTN_SENT_TO_TAXPAYER)) as TAX_RTN_SENT_TO_TAXPAYER,
office_name as office_name,
TO_CHAR(NULL)
FROM PARTNER_SUMMARY_VW
GROUP BY partner,office_name
UNION ALL
SELECT total_text,
force_bold_fn(a) as a,
force_bold_fn(TOTAL_ORG_SENT) as TOTAL_ORG_SENT,
force_bold_fn(TOTAL_ORG_RECEIVED) as TOTAL_ORG_RECEIVED,
force_bold_fn(TOTAL_ORG_PROCESS) as TOTAL_ORG_PROCESS,
force_bold_fn(TOTAL_ORG_REVIEW) as TOTAL_ORG_REVIEW,
force_bold_fn(TOTAL_ORG_CLIENT) as TOTAL_ORG_CLIENT,
TO_CHAR(NULL),
force_bold_fn(TOTAL_ORG_PARTNER) as TOTAL_ORG_PARTNER
FROM gts_totals_vw

and I got the above error

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

Admin — Tue, 15 Jun 2010 09:40:11 GMT

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;