pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate view
3+ messages / 2 participants
[nested] [flat]
* create view
@ 2019-09-17 18:13 Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 2 replies; 3+ messages in thread
From: Pepe TD Vo @ 2019-09-17 18:13 UTC (permalink / raw)
To: pgsql-admin
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) download
view image
1568741119986blob.jpg (image/png, 19.2K) download
view image
1568741152420blob.jpg (image/png, 22.3K) download
view image
1568741375231blob.jpg (image/png, 10.2K) download
view image
1568742317100blob.jpg (image/png, 14.5K) download
view image
1568743990863blob.jpg (image/png, 66.9K) download
view image
* Re: create view
@ 2019-09-17 19:12 Ron <ronljohnsonjr@gmail.com>
parent: Pepe TD Vo <pepevo@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ron @ 2019-09-17 19:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 9/17/19 1:13 PM, Pepe TD Vo 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,
[snip]
> ERROR: column ap.application_id does not exist
> LINE 50: INNER JOIN "ECISDRDM"."BNFT_CURR_FACT"bcf ON (ap.applicatio...
> ^
> SQL state: 42703
> Character: 864
[snip]
> I do a simple select query of application_cdim table, the result is fine:
>
> Select *
> FROM "ECISDRDM"."APPLICATION_CDIM" ap
When you have a weird error on a long or statement, simplify, simplify,
simplify.
Make a simple test view that's just on "ECISDRDM"."APPLICATION_CDIM". Then
make another test view that's just a join between those two tables.
Simplify everything as much as possible be only selecting a few columns, etc.
That might show where your error is.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: create view
@ 2019-09-17 19:13 Pepe TD Vo <pepevo@yahoo.com>
parent: Pepe TD Vo <pepevo@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Pepe TD Vo @ 2019-09-17 19:13 UTC (permalink / raw)
To: pgsql-admin
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) download
view image
1568741375231blob.jpg (image/png, 10.2K) download
view image
1568742317100blob.jpg (image/png, 14.5K) download
view image
1568741119986blob.jpg (image/png, 19.2K) download
view image
1568743990863blob.jpg (image/png, 66.9K) download
view image
1568741152420blob.jpg (image/png, 22.3K) download
view image
end of thread, other threads:[~2019-09-17 19:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-17 18:13 create view Pepe TD Vo <pepevo@yahoo.com>
2019-09-17 19:12 ` Ron <ronljohnsonjr@gmail.com>
2019-09-17 19:13 ` Pepe TD Vo <pepevo@yahoo.com>
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