I figured out, thank you.have a good day.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, September 17, 2019, 02:13:21 PM EDT, Pepe TD Vo <pepevo(at)yahoo(dot)com> wrote:
Hello experts,
I need to create view from schema's tables and when I run I get an error:
CREATE or REPLACE view "ECISDRDM"."BENE_VW" ( receipt_number, service_center, form_number, a_number, ssn, last_name, first_name, middle_name, date_of_birth, sex, country_of_birth, country_of_citizenship, country_of_residence, street, city, state, zip, province, postal_code, country, last_arrival, prty_typ_id, mig_filename) AS SELECT DISTINCT ( ap.receipt_number, scc.svc_ctr_crc_cd, f.frm_nbr_std_cd, bene.a_nbr, bene.ssn, bene.last_nm, bene.frst_nm, bene.mid_nm, bene.dob_id, bene.gndr, bcf.bene_cntry_of_brth_id, ctry.cntry_st_5_dgt_src_cd, bcf.bene_cntry_of_rsdc_id, bene.addr_1, bene.cty, bene.st_prvn, bene.pstl_cd, bene.st_prvn, bene.pstl_cd, bene.cntry, bene.last_arrival, bene.prty_typ_id, bene.mig_filename)FROM "ECISDRDM"."APPLICATION_CDIM" apINNER JOIN "ECISDRDM"."BNFT_CURR_FACT" bcf ON (ap.application_id = bcf.application_id)INNER JOIN "ECISDRDM"."PRTY_CDIM" bene ON (bcf.bene_id = bene.prty_id)INNER JOIN "ECISDRDM"."CNTRY_ST_CDIM" ctry ON (bcf.bene_cntry_of_brth_id = ctry.cntry_st_id)INNER JOIN "ECISDRDM"."SVC_CTR_CDIM" scc ON (scc.svc_ctr_id = bcf.svc_ctr_id)INNER JOIN "ECISDRDM"."FRM_CDIM" f ON (f.frm_id = bcf.frm_id)WHERE bene.prty_typ_id = 1;
ERROR: column ap.application_id does not existLINE 50: INNER JOIN "ECISDRDM"."BNFT_CURR_FACT" bcf ON (ap.applicatio... ^SQL state: 42703Character: 864
what I did wrong here and I used ora2pg to convert from oracle to postgres, they looked the same accept no schema_name in front of each table.
Also, when I type "create view col1, col2, state, etc....look like "state" didn't take it... and when I put "states" then it's ok for the syntax.
Why column, application_id does not exist?
I do a simple select query of application_cdim table, the result is fine:
Select *FROM "ECISDRDM"."APPLICATION_CDIM" ap
even I narrow down the column and put in a double quote, still complain error.
thank you for your help.
v/r,
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
