asked on Error in creating a view.
I am trying to create a view using the code below and I am getting error, ora-00904: "ACTUAL_END_DATE": invalid identifier.
I have modified the table_Names so that they do not represent the actual tables.
create or replace view reqs_11 as
select i.user_dm8_cd, i.div_cd, i.itmz_ln_lnum, i.actg_ln_lnum, i.hdr_lnum, i.doc_num, i.titl, i.fisc_year, i.doc_dt,
i.dtyp_cat, i.fund, i.prog_cd, i.psp_cd, i.orig_doc_dt, max(i.last_modf_dt), i.scty_prnc_nm, i.nm_of_prsn, i.stae_of_line,
i.dtyp_dfnd_hd4, i.rcvd_qy, i.unpr, i.doc_type_cd, i.fund_cd, i.bbfy, i.ebfy, i.dscr, i.ln_desc, i.oplk, i.bdob_cd,
i.orgn_cd, i.orgn_grp_cd, i.doc_stus, i.clsd_dt, i.rqsd_am, i.clsd_am, i.dllr_am, i.clsd_lnam, i.fisc_mnth, i.acty_cd,
i.acty_nm, i.asgn_dt, i.ctrc_splt, i.orig_asgt_dt, i.last_asgt_dt, i.bdob_nm, i.uf_requ_date, i.size_of_smal_busn,
i.addr_cd, i.duns_plus_fr_num, i.perd_perf_stdt, i.perd_perf_endt, i.prod_svc_cd, i.prod_svc_dscr, i.prod_svc_nm,
i.offc_typ, i.offc_id, dlvr_dt, i.duns_num, i.tax_idnt_num, i.agre_num, i.agre_lnum, i.last_usr_id, i.proj_cd,
i.user_dm5_cd, i.sec_class_cd, i.doc_key, i.div_nm, i.orgn_grp_nm, i.prog_nm, i.user_dm8_nm, i.orgn_nm, i.cmdt_cd, i.cmdt_nm,
i.bdob_cat_cd, i.bdob_cat_nm, orgn_clas_cd, orgn_clas_nm, orgn_cat_cd, orgn_cat_nm, sbob_cd, sbob_nm, vend_cd,
i.vend_nm, i.user_dm1_cd, i.asgt_stus, i.addr_nm, i.sub_proj_cd, i.sub_proj_nm,
w.mlst_stus as status, w.mlst_num as milestone_number, w.mlst_dscr as description, w.mlst_stdt as start_date,
w.cmpl_dt as completion_date, w.estm_cmpd_dt as expected_end_date, w.mlst_actl_endt as actual_end_date
from vw_iq i, vw_wkld_asgn w
Where i.doc_key = w.doc_key (+)
Group by i.user_dm8_cd, i.div_cd, i.itmz_ln_lnum, i.actg_ln_lnum, i.hdr_lnum, i.doc_num, i.titl, i.fisc_year, i.doc_dt,
i.dtyp_cat, i.fund, i.prog_cd, i.psp_cd, i.orig_doc_dt, i.scty_prnc_nm, i.nm_of_prsn, i.stae_of_line,
i.dtyp_dfnd_hd4, i.rcvd_qy, i.unpr, i.doc_type_cd, i.fund_cd, i.bbfy, i.ebfy, i.dscr, i.ln_desc, i.oplk, i.bdob_cd,
i.orgn_cd, i.orgn_grp_cd, i.doc_stus, i.clsd_dt, i.rqsd_am, i.clsd_am, i.dllr_am, i.clsd_lnam, i.fisc_mnth, i.acty_cd,
i.acty_nm, i.asgn_dt, i.ctrc_splt, i.orig_asgt_dt, i.last_asgt_dt, i.bdob_nm, i.uf_requ_date, i.size_of_smal_busn,
i.addr_cd, i.duns_plus_fr_num, i.perd_perf_stdt, i.perd_perf_endt, i.prod_svc_cd, i.prod_svc_dscr, i.prod_svc_nm,
i.offc_typ, i.offc_id, dlvr_dt, i.duns_num, i.tax_idnt_num, i.agre_num, i.agre_lnum, i.last_usr_id, i.proj_cd,
i.user_dm5_cd, i.sec_class_cd, i.doc_key, i.div_nm, i.orgn_grp_nm, i.prog_nm, i.user_dm8_nm, i.orgn_nm, i.cmdt_cd, i.cmdt_nm,
i.bdob_cat_cd, i.bdob_cat_nm, orgn_clas_cd, orgn_clas_nm, orgn_cat_cd, orgn_cat_nm, sbob_cd, sbob_nm, vend_cd,
i.vend_nm, i.user_dm1_cd, i.asgt_stus, i.addr_nm, i.sub_proj_cd, i.sub_proj_nm,
status, milestone_number, description, start_date, completion_date, expected_end_date, actual_end_date;
I have modified the table_Names so that they do not represent the actual tables.
create or replace view reqs_11 as
select i.user_dm8_cd, i.div_cd, i.itmz_ln_lnum, i.actg_ln_lnum, i.hdr_lnum, i.doc_num, i.titl, i.fisc_year, i.doc_dt,
i.dtyp_cat, i.fund, i.prog_cd, i.psp_cd, i.orig_doc_dt, max(i.last_modf_dt), i.scty_prnc_nm, i.nm_of_prsn, i.stae_of_line,
i.dtyp_dfnd_hd4, i.rcvd_qy, i.unpr, i.doc_type_cd, i.fund_cd, i.bbfy, i.ebfy, i.dscr, i.ln_desc, i.oplk, i.bdob_cd,
i.orgn_cd, i.orgn_grp_cd, i.doc_stus, i.clsd_dt, i.rqsd_am, i.clsd_am, i.dllr_am, i.clsd_lnam, i.fisc_mnth, i.acty_cd,
i.acty_nm, i.asgn_dt, i.ctrc_splt, i.orig_asgt_dt, i.last_asgt_dt, i.bdob_nm, i.uf_requ_date, i.size_of_smal_busn,
i.addr_cd, i.duns_plus_fr_num, i.perd_perf_stdt, i.perd_perf_endt, i.prod_svc_cd, i.prod_svc_dscr, i.prod_svc_nm,
i.offc_typ, i.offc_id, dlvr_dt, i.duns_num, i.tax_idnt_num, i.agre_num, i.agre_lnum, i.last_usr_id, i.proj_cd,
i.user_dm5_cd, i.sec_class_cd, i.doc_key, i.div_nm, i.orgn_grp_nm, i.prog_nm, i.user_dm8_nm, i.orgn_nm, i.cmdt_cd, i.cmdt_nm,
i.bdob_cat_cd, i.bdob_cat_nm, orgn_clas_cd, orgn_clas_nm, orgn_cat_cd, orgn_cat_nm, sbob_cd, sbob_nm, vend_cd,
i.vend_nm, i.user_dm1_cd, i.asgt_stus, i.addr_nm, i.sub_proj_cd, i.sub_proj_nm,
w.mlst_stus as status, w.mlst_num as milestone_number, w.mlst_dscr as description, w.mlst_stdt as start_date,
w.cmpl_dt as completion_date, w.estm_cmpd_dt as expected_end_date, w.mlst_actl_endt as actual_end_date
from vw_iq i, vw_wkld_asgn w
Where i.doc_key = w.doc_key (+)
Group by i.user_dm8_cd, i.div_cd, i.itmz_ln_lnum, i.actg_ln_lnum, i.hdr_lnum, i.doc_num, i.titl, i.fisc_year, i.doc_dt,
i.dtyp_cat, i.fund, i.prog_cd, i.psp_cd, i.orig_doc_dt, i.scty_prnc_nm, i.nm_of_prsn, i.stae_of_line,
i.dtyp_dfnd_hd4, i.rcvd_qy, i.unpr, i.doc_type_cd, i.fund_cd, i.bbfy, i.ebfy, i.dscr, i.ln_desc, i.oplk, i.bdob_cd,
i.orgn_cd, i.orgn_grp_cd, i.doc_stus, i.clsd_dt, i.rqsd_am, i.clsd_am, i.dllr_am, i.clsd_lnam, i.fisc_mnth, i.acty_cd,
i.acty_nm, i.asgn_dt, i.ctrc_splt, i.orig_asgt_dt, i.last_asgt_dt, i.bdob_nm, i.uf_requ_date, i.size_of_smal_busn,
i.addr_cd, i.duns_plus_fr_num, i.perd_perf_stdt, i.perd_perf_endt, i.prod_svc_cd, i.prod_svc_dscr, i.prod_svc_nm,
i.offc_typ, i.offc_id, dlvr_dt, i.duns_num, i.tax_idnt_num, i.agre_num, i.agre_lnum, i.last_usr_id, i.proj_cd,
i.user_dm5_cd, i.sec_class_cd, i.doc_key, i.div_nm, i.orgn_grp_nm, i.prog_nm, i.user_dm8_nm, i.orgn_nm, i.cmdt_cd, i.cmdt_nm,
i.bdob_cat_cd, i.bdob_cat_nm, orgn_clas_cd, orgn_clas_nm, orgn_cat_cd, orgn_cat_nm, sbob_cd, sbob_nm, vend_cd,
i.vend_nm, i.user_dm1_cd, i.asgt_stus, i.addr_nm, i.sub_proj_cd, i.sub_proj_nm,
status, milestone_number, description, start_date, completion_date, expected_end_date, actual_end_date;
Oracle Database
Last Comment
Kamal Agnihotri
That is because you cannot use a column alias in a group by. The column has not gotten to the select portion of the code and been renamed into your alias. You need to use the original column name.
I believe this should work:
I believe this should work:
CREATE OR replace VIEW reqs_11 AS
SELECT i.user_dm8_cd,
i.div_cd,
i.itmz_ln_lnum,
i.actg_ln_lnum,
i.hdr_lnum,
i.doc_num,
i.titl,
i.fisc_year,
i.doc_dt,
i.dtyp_cat,
i.fund,
i.prog_cd,
i.psp_cd,
i.orig_doc_dt,
Max(i.last_modf_dt),
i.scty_prnc_nm,
i.nm_of_prsn,
i.stae_of_line,
i.dtyp_dfnd_hd4,
i.rcvd_qy,
i.unpr,
i.doc_type_cd,
i.fund_cd,
i.bbfy,
i.ebfy,
i.dscr,
i.ln_desc,
i.oplk,
i.bdob_cd,
i.orgn_cd,
i.orgn_grp_cd,
i.doc_stus,
i.clsd_dt,
i.rqsd_am,
i.clsd_am,
i.dllr_am,
i.clsd_lnam,
i.fisc_mnth,
i.acty_cd,
i.acty_nm,
i.asgn_dt,
i.ctrc_splt,
i.orig_asgt_dt,
i.last_asgt_dt,
i.bdob_nm,
i.uf_requ_date,
i.size_of_smal_busn,
i.addr_cd,
i.duns_plus_fr_num,
i.perd_perf_stdt,
i.perd_perf_endt,
i.prod_svc_cd,
i.prod_svc_dscr,
i.prod_svc_nm,
i.offc_typ,
i.offc_id,
dlvr_dt,
i.duns_num,
i.tax_idnt_num,
i.agre_num,
i.agre_lnum,
i.last_usr_id,
i.proj_cd,
i.user_dm5_cd,
i.sec_class_cd,
i.doc_key,
i.div_nm,
i.orgn_grp_nm,
i.prog_nm,
i.user_dm8_nm,
i.orgn_nm,
i.cmdt_cd,
i.cmdt_nm,
i.bdob_cat_cd,
i.bdob_cat_nm,
orgn_clas_cd,
orgn_clas_nm,
orgn_cat_cd,
orgn_cat_nm,
sbob_cd,
sbob_nm,
vend_cd,
i.vend_nm,
i.user_dm1_cd,
i.asgt_stus,
i.addr_nm,
i.sub_proj_cd,
i.sub_proj_nm,
w.mlst_stus AS status,
w.mlst_num AS milestone_number,
w.mlst_dscr AS description,
w.mlst_stdt AS start_date,
w.cmpl_dt AS completion_date,
w.estm_cmpd_dt AS expected_end_date,
w.mlst_actl_endt AS actual_end_date
FROM vw_iq i,
vw_wkld_asgn w
WHERE i.doc_key = w.doc_key (+)
GROUP BY i.user_dm8_cd,
i.div_cd,
i.itmz_ln_lnum,
i.actg_ln_lnum,
i.hdr_lnum,
i.doc_num,
i.titl,
i.fisc_year,
i.doc_dt,
i.dtyp_cat,
i.fund,
i.prog_cd,
i.psp_cd,
i.orig_doc_dt,
i.scty_prnc_nm,
i.nm_of_prsn,
i.stae_of_line,
i.dtyp_dfnd_hd4,
i.rcvd_qy,
i.unpr,
i.doc_type_cd,
i.fund_cd,
i.bbfy,
i.ebfy,
i.dscr,
i.ln_desc,
i.oplk,
i.bdob_cd,
i.orgn_cd,
i.orgn_grp_cd,
i.doc_stus,
i.clsd_dt,
i.rqsd_am,
i.clsd_am,
i.dllr_am,
i.clsd_lnam,
i.fisc_mnth,
i.acty_cd,
i.acty_nm,
i.asgn_dt,
i.ctrc_splt,
i.orig_asgt_dt,
i.last_asgt_dt,
i.bdob_nm,
i.uf_requ_date,
i.size_of_smal_busn,
i.addr_cd,
i.duns_plus_fr_num,
i.perd_perf_stdt,
i.perd_perf_endt,
i.prod_svc_cd,
i.prod_svc_dscr,
i.prod_svc_nm,
i.offc_typ,
i.offc_id,
dlvr_dt,
i.duns_num,
i.tax_idnt_num,
i.agre_num,
i.agre_lnum,
i.last_usr_id,
i.proj_cd,
i.user_dm5_cd,
i.sec_class_cd,
i.doc_key,
i.div_nm,
i.orgn_grp_nm,
i.prog_nm,
i.user_dm8_nm,
i.orgn_nm,
i.cmdt_cd,
i.cmdt_nm,
i.bdob_cat_cd,
i.bdob_cat_nm,
orgn_clas_cd,
orgn_clas_nm,
orgn_cat_cd,
orgn_cat_nm,
sbob_cd,
sbob_nm,
vend_cd,
i.vend_nm,
i.user_dm1_cd,
i.asgt_stus,
i.addr_nm,
i.sub_proj_cd,
i.sub_proj_nm,
w.mlst_stus,
w.mlst_num,
w.mlst_dscr,
w.mlst_stdt,
w.cmpl_dt,
w.estm_cmpd_dt,
w.mlst_actl_endt;
ASKER
Hi Johnsone/slightwv,
I will implement your solution when I am at work. I will update when I do that.
Thanks for the quick reply.
-- Kamal
I will implement your solution when I am at work. I will update when I do that.
Thanks for the quick reply.
-- Kamal
ASKER
I made the modification as suggested by johnsone, I am getting ORA-00998: must name this expression with a column alias.
and when I have status, milestone_number, description, start_date, completion_date, expected_end_date, actual_end_date;
I get ORA:00904: "actual_end_date": invalid identifier
I replaced actual_end_date with w.mlst_actl_enddt, leaving rest of the column same, I
I get ORA:00904: "expected_end_date": invalid identifier
I replaced all the columns as suggested to:
w.mlst_stus, w.mlst_num, w.mlst_dscr, w.mlst_stdt, w.cmpl_dt, w.estm_cmpd_dt, w.mlst_actl_endt
I get ORA-00998: must name this expression with a column alias.
I have tried all 3 possibilities,
w.mlst_stus as status, w.mlst_num as milestone_number, w.mlst_dscr as description, w.mlst_stdt as start_date,
w.cmpl_dt as completion_date, w.estm_cmpd_dt as expected_end_date, w.mlst_actl_endt as actual_end_date
gives error: ORA-00933: SQL command not ended properly
Please help.
Thanks.
and when I have status, milestone_number, description, start_date, completion_date, expected_end_date, actual_end_date;
I get ORA:00904: "actual_end_date": invalid identifier
I replaced actual_end_date with w.mlst_actl_enddt, leaving rest of the column same, I
I get ORA:00904: "expected_end_date": invalid identifier
I replaced all the columns as suggested to:
w.mlst_stus, w.mlst_num, w.mlst_dscr, w.mlst_stdt, w.cmpl_dt, w.estm_cmpd_dt, w.mlst_actl_endt
I get ORA-00998: must name this expression with a column alias.
I have tried all 3 possibilities,
w.mlst_stus as status, w.mlst_num as milestone_number, w.mlst_dscr as description, w.mlst_stdt as start_date,
w.cmpl_dt as completion_date, w.estm_cmpd_dt as expected_end_date, w.mlst_actl_endt as actual_end_date
gives error: ORA-00933: SQL command not ended properly
Please help.
Thanks.
Keep the alias in the SELECT portion of the view. You need the actual column in the GROUP BY part of the query.
For us to provide 100% working and tested code, we would need all the table definitions used in the view.
For us to provide 100% working and tested code, we would need all the table definitions used in the view.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
The reason for the ORA-0098 (the alias issue), is because you are creating a view. One of the columns you are selecting is a function, MAX, and you cannot use that as a column name. You must create an alias on the one line of the create view statement where the MAX is. Once you do that it should work. Or at least it does with the dummy tables I created.
ASKER
Slightwv, Thanks. Your idea produced the expected result.
Change:
status, milestone_number, description, start_date, completion_date, expected_end_date, actual_end_date;
to:
status, milestone_number, description, start_date, completion_date, expected_end_date, w.mlst_actl_endt;
Same for all the other aliases...