Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1896
  • Last Modified:

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

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
mmatharu
Asked:
mmatharu
  • 3
1 Solution
 
nagkiCommented:
try this:

replace SUM (k.reconciliation_amount),   with
sum(k.reconcilation_amount) sum1
0
 
mmatharuAuthor Commented:
Thank you that worked perfectly, and I'm going to accept your ans, but could you send me an explaination
0
 
nagkiCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
nagkiCommented:
Hi slightwv..

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now