Thread: create view
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" ap
INNER 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 exist
LINE 50: INNER JOIN "ECISDRDM"."BNFT_CURR_FACT" bcf ON (ap.applicatio...
^
SQL state: 42703
Character: 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.
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
Attachment
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.
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" ap
INNER 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 exist
LINE 50: INNER JOIN "ECISDRDM"."BNFT_CURR_FACT" bcf ON (ap.applicatio...
^
SQL state: 42703
Character: 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.
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