Link to home
Start Free TrialLog in
Avatar of mmatharu
mmatharu

asked on

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
Avatar of nagki
nagki

try this:

replace SUM (k.reconciliation_amount),   with
sum(k.reconcilation_amount) sum1
Avatar of mmatharu

ASKER

Thank you that worked perfectly, and I'm going to accept your ans, but could you send me an explaination
ASKER CERTIFIED SOLUTION
Avatar of nagki
nagki

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
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.
Hi slightwv..

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