Re: create view

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 <[email protected]> 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