From: Pepe TD Vo <pepevo@yahoo.com>
To: Pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: create view
Date: Tue, 17 Sep 2019 19:13:29 +0000 (UTC)
Message-ID: <1259833290.5887158.1568747610605@mail.yahoo.com> (raw)
In-Reply-To: <1896838771.5876030.1568744001412@mail.yahoo.com>
References: <1896838771.5876030.1568744001412.ref@mail.yahoo.com>
<1896838771.5876030.1568744001412@mail.yahoo.com>
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@yahoo.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
Attachments:1568741026700blob.jpg (image/png, 31.1K) downloadview image1568741375231blob.jpg (image/png, 10.2K) downloadview image1568742317100blob.jpg (image/png, 14.5K) downloadview image1568741119986blob.jpg (image/png, 19.2K) downloadview image1568743990863blob.jpg (image/png, 66.9K) downloadview image1568741152420blob.jpg (image/png, 22.3K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pepevo@yahoo.com
Subject: Re: create view
In-Reply-To: <1259833290.5887158.1568747610605@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox