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_s ort,
f.data_object_recon_type_s ort, 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_it em,
setup.data_object_recon_st age d,
setup.data_object_recon_ty pe f,
setup.data_object_recon_un it e,
setup.recon_item i,
setup.recon_stage h,
setup.recon_type g,
setup.recon_unit j,
environ.amount_reconciliat ion k,
environ.recon_figure,
environ.ticket_set a,
environ.ticket_set_run_rer un c
WHERE ( (b.data_object_code = data_object_recon_item.dat a_object_c ode)
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.rec on_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_i d)
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_s ort,
f.data_object_recon_type_s ort,
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
and where the SUM (k.reconciliation_amount),
CREATE OR REPLACE VIEW ENVIRON.REC_AMT_TICKET_SET
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_
f.data_object_recon_type_s
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),
FROM setup.data_object b,
setup.data_object_recon_it
setup.data_object_recon_st
setup.data_object_recon_ty
setup.data_object_recon_un
setup.recon_item i,
setup.recon_stage h,
setup.recon_type g,
setup.recon_unit j,
environ.amount_reconciliat
environ.recon_figure,
environ.ticket_set a,
environ.ticket_set_run_rer
WHERE ( (b.data_object_code = data_object_recon_item.dat
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.rec
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_i
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
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
)
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_
e.data_object_recon_unit_s
f.data_object_recon_type_s
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
ASKER
Thank you that worked perfectly, and I'm going to accept your ans, but could you send me an explaination
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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..
Thanx for ur comment..but i won't comment further..i will leave it to mmatharu..
replace SUM (k.reconciliation_amount),
sum(k.reconcilation_amount