?
Solved

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

Posted on 2005-03-31
5
Medium Priority
?
1,658 Views
Last Modified: 2011-09-20
I am createing this view, and getting this error:  ORA-00998: must name this expression with a column alias

and where the SUM (k.reconciliation_amount),  IS GETTING HIGHLIGHTED. any suggestions

CREATE OR REPLACE VIEW ENVIRON.REC_AMT_TICKET_SET_SH_VIEW
AS
SELECT   a.initial_fileset_id, a.ticket_set_key, a.business_process_type,
         a.country_code, a.company_code, a.project_code, b.data_object_code,
         c.run_id, b.data_object_name, b.data_object_description,
         d.data_object_recon_stage_sort, e.data_object_recon_unit_sort,
         f.data_object_recon_type_sort, g.recon_type_name, g.recon_type_group,
         g.recon_type_sub_group, h.recon_stage_name, h.recon_stage_system,
         h.recon_stage_function, i.recon_item_name, i.recon_by_amount,
         i.recon_by_count, j.recon_unit_name, a.reconciliation_status,
         SUM (k.reconciliation_amount), k.currency_type
    FROM setup.data_object b,
         setup.data_object_recon_item,
         setup.data_object_recon_stage d,
         setup.data_object_recon_type f,
         setup.data_object_recon_unit e,
         setup.recon_item i,
         setup.recon_stage h,
         setup.recon_type g,
         setup.recon_unit j,
         environ.amount_reconciliation k,
         environ.recon_figure,
         environ.ticket_set a,
         environ.ticket_set_run_rerun c
   WHERE (    (b.data_object_code = data_object_recon_item.data_object_code)
          AND (b.data_object_code = d.data_object_code)
          AND (b.data_object_code = f.data_object_code)
          AND (b.data_object_code = e.data_object_code)
          AND (i.recon_item_id = data_object_recon_item.recon_item_id)
          AND (h.recon_stage_id = d.recon_stage_id)
          AND (g.recon_type_id = f.recon_type_id)
          AND (j.recon_unit_id = e.recon_unit_id)
          AND (i.recon_item_id = recon_figure.recon_item_id)
          AND (h.recon_stage_id = recon_figure.recon_stage_id)
          AND (g.recon_type_id = recon_figure.recon_type_id)
          AND (j.recon_unit_id = recon_figure.recon_unit_id)
          AND (recon_figure.recon_figure_id = k.recon_figure_id)
          AND (b.data_object_code = c.data_object_code)
          AND (a.initial_fileset_id = c.initial_fileset_id)
          AND (a.ticket_set_key = c.ticket_set_key)
          AND (c.ticket_set_run_rerun_id = k.ticket_set_run_rerun_id)
         )
GROUP BY a.initial_fileset_id,
         a.ticket_set_key,
         a.business_process_type,
         a.country_code,
         a.company_code,
         a.project_code,
         b.data_object_code,
         c.run_id,
         b.data_object_name,
         b.data_object_description,
         d.data_object_recon_stage_sort,
         e.data_object_recon_unit_sort,
         f.data_object_recon_type_sort,
         g.recon_type_name,
         g.recon_type_group,
         g.recon_type_sub_group,
         h.recon_stage_name,
         h.recon_stage_system,
         h.recon_stage_function,
         i.recon_item_name,
         i.recon_by_amount,
         i.recon_by_count,
         j.recon_unit_name,
         a.reconciliation_status,
         k.currency_type
0
Comment
Question by:mmatharu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 8

Expert Comment

by:nagki
ID: 13669716
try this:

replace SUM (k.reconciliation_amount),   with
sum(k.reconcilation_amount) sum1
0
 

Author Comment

by:mmatharu
ID: 13669787
Thank you that worked perfectly, and I'm going to accept your ans, but could you send me an explaination
0
 
LVL 8

Accepted Solution

by:
nagki earned 500 total points
ID: 13669835
Hi..

As u r creating a view..and u r selecting some columns..Those column names should be valid identifiers.

SUM (k.reconciliation_amount) is not an valid identifier.

An valid identifier should not contain either of these (,) characters..

Hope this helps!
Nagki  
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13671707
Please give nagki full credit for the answer.  I just wanted to further clarify their post (just in case there is wtill any confusion).

Creating a view is similar to creating a table.  The columns have to have a valid name.  When you do not specify a name when creating a view, Oracle just uses the names from the result set as the column names for the view.  When using a function such as SUM(...) without an alias on the column, Oracle tries to create a column named:  SUM(...).  Paranthesis' aren't allowed in column names.
0
 
LVL 8

Expert Comment

by:nagki
ID: 13687089
Hi slightwv..

Thanx for ur comment..but i won't comment further..i will leave it to mmatharu..
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question