create view

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

create view

Pepe TD Vo
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.
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

1568741026700blob.jpg (42K) Download Attachment
1568741119986blob.jpg (26K) Download Attachment
1568741152420blob.jpg (30K) Download Attachment
1568741375231blob.jpg (13K) Download Attachment
1568742317100blob.jpg (19K) Download Attachment
1568743990863blob.jpg (91K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: create view

Ron-2
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.
Reply | Threaded
Open this post in threaded view
|

Re: create view

Pepe TD Vo
In reply to this post by Pepe TD Vo
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 <[hidden email]> 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.
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

1568741026700blob.jpg (42K) Download Attachment
1568741375231blob.jpg (13K) Download Attachment
1568742317100blob.jpg (19K) Download Attachment
1568741119986blob.jpg (26K) Download Attachment
1568743990863blob.jpg (91K) Download Attachment
1568741152420blob.jpg (30K) Download Attachment