Solved

PostgreSQL - amend a VIEW script to include another tabel

Posted on 2016-10-11
5
49 Views
Last Modified: 2016-10-17
This script work perfectly, and I want to include two more columns from another table. The table details are included at the end. (I want to be able to see the contents of table: mb_2011_code in another column in the VIEW).
Any help will be much appreciated.
Thanks.

CREATE OR REPLACE VIEW ADDRESS_VIEW

AS

SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME,

AD.LOT_NUMBER_PREFIX as LOT_NUMBER_PREFIX,
AD.LOT_NUMBER as LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX as LOT_NUMBER_SUFFIX,

FTA.NAME as FLAT_TYPE,
AD.FLAT_NUMBER_PREFIX as FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER as FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX as FLAT_NUMBER_SUFFIX,

LTA.NAME as LEVEL_TYPE,
AD.LEVEL_NUMBER_PREFIX as LEVEL_NUMBER_PREFIX,
AD.LEVEL_NUMBER as LEVEL_NUMBER,
AD.LEVEL_NUMBER_SUFFIX as LEVEL_NUMBER_SUFFIX,

AD.NUMBER_FIRST_PREFIX as NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST as NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX as NUMBER_FIRST_SUFFIX,
AD.NUMBER_LAST_PREFIX as NUMBER_LAST_PREFIX,
AD.NUMBER_LAST as NUMBER_LAST,
AD.NUMBER_LAST_SUFFIX as NUMBER_LAST_SUFFIX,

SL.STREET_NAME as STREET_NAME,
SL.STREET_CLASS_CODE as STREET_CLASS_CODE,
SCA.NAME as STREET_CLASS_TYPE,
SL.STREET_TYPE_CODE as STREET_TYPE_CODE,
SL.STREET_SUFFIX_CODE as STREET_SUFFIX_CODE,
SSA.NAME as STREET_SUFFIX_TYPE,

L.LOCALITY_NAME as LOCALITY_NAME,

ST.STATE_ABBREVIATION as STATE_ABBREVIATION,

AD.POSTCODE as POSTCODE,

ADG.LATITUDE as LATITUDE,
ADG.LONGITUDE as LONGITUDE,
GTA.NAME as GEOCODE_TYPE,

AD.CONFIDENCE as CONFIDENCE,

AD.ALIAS_PRINCIPAL as ALIAS_PRINCIPAL,
AD.PRIMARY_SECONDARY as PRIMARY_SECONDARY,

AD.LEGAL_PARCEL_ID as LEGAL_PARCEL_ID,

AD.DATE_CREATED as DATE_CREATED

FROM

ADDRESS_DETAIL AD
LEFT JOIN FLAT_TYPE_AUT FTA ON AD.FLAT_TYPE_CODE=FTA.CODE
LEFT JOIN LEVEL_TYPE_AUT LTA ON AD.LEVEL_TYPE_CODE=LTA.CODE
JOIN STREET_LOCALITY SL ON AD.STREET_LOCALITY_PID=SL.STREET_LOCALITY_PID
LEFT JOIN STREET_SUFFIX_AUT SSA ON SL.STREET_SUFFIX_CODE=SSA.CODE
LEFT JOIN STREET_CLASS_AUT SCA ON SL.STREET_CLASS_CODE=SCA.CODE
LEFT JOIN STREET_TYPE_AUT STA ON SL.STREET_TYPE_CODE=STA.CODE
JOIN LOCALITY L ON AD.LOCALITY_PID = L.LOCALITY_PID
JOIN ADDRESS_DEFAULT_GEOCODE ADG ON AD.ADDRESS_DETAIL_PID=ADG.ADDRESS_DETAIL_PID
LEFT JOIN GEOCODE_TYPE_AUT GTA ON ADG.GEOCODE_TYPE_CODE=GTA.CODE
LEFT JOIN GEOCODED_LEVEL_TYPE_AUT GLTA ON AD.LEVEL_GEOCODED_CODE=GLTA.CODE
JOIN STATE ST ON L.STATE_PID=ST.STATE_PID

WHERE
AD.CONFIDENCE > -1;

Tables:
mb_2011
      Columns (2)
            mb_2011_pid
            mb_2011_code

mb_match_code_aut
      Columns (3)
           code
           name
           description
0
Comment
Question by:gregfthompson
  • 3
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 41839096
What is the relation between mb_2011 and any of your tables in the view definition?
Do you have any joining column. If so, you can add it as another LEFT JOIN.
CREATE OR REPLACE VIEW ADDRESS_VIEW

AS

SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME,

AD.LOT_NUMBER_PREFIX as LOT_NUMBER_PREFIX,
AD.LOT_NUMBER as LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX as LOT_NUMBER_SUFFIX,

FTA.NAME as FLAT_TYPE,
AD.FLAT_NUMBER_PREFIX as FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER as FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX as FLAT_NUMBER_SUFFIX,

LTA.NAME as LEVEL_TYPE,
AD.LEVEL_NUMBER_PREFIX as LEVEL_NUMBER_PREFIX,
AD.LEVEL_NUMBER as LEVEL_NUMBER,
AD.LEVEL_NUMBER_SUFFIX as LEVEL_NUMBER_SUFFIX,

AD.NUMBER_FIRST_PREFIX as NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST as NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX as NUMBER_FIRST_SUFFIX,
AD.NUMBER_LAST_PREFIX as NUMBER_LAST_PREFIX,
AD.NUMBER_LAST as NUMBER_LAST,
AD.NUMBER_LAST_SUFFIX as NUMBER_LAST_SUFFIX,

SL.STREET_NAME as STREET_NAME,
SL.STREET_CLASS_CODE as STREET_CLASS_CODE,
SCA.NAME as STREET_CLASS_TYPE,
SL.STREET_TYPE_CODE as STREET_TYPE_CODE,
SL.STREET_SUFFIX_CODE as STREET_SUFFIX_CODE,
SSA.NAME as STREET_SUFFIX_TYPE,

L.LOCALITY_NAME as LOCALITY_NAME,

ST.STATE_ABBREVIATION as STATE_ABBREVIATION,

AD.POSTCODE as POSTCODE,

ADG.LATITUDE as LATITUDE,
ADG.LONGITUDE as LONGITUDE,
GTA.NAME as GEOCODE_TYPE,

AD.CONFIDENCE as CONFIDENCE,

AD.ALIAS_PRINCIPAL as ALIAS_PRINCIPAL,
AD.PRIMARY_SECONDARY as PRIMARY_SECONDARY,

AD.LEGAL_PARCEL_ID as LEGAL_PARCEL_ID,

AD.DATE_CREATED as DATE_CREATED,

MB.mb_2011_pid,
MB.mb_2011_code

FROM

ADDRESS_DETAIL AD 
LEFT JOIN FLAT_TYPE_AUT FTA ON AD.FLAT_TYPE_CODE=FTA.CODE
LEFT JOIN LEVEL_TYPE_AUT LTA ON AD.LEVEL_TYPE_CODE=LTA.CODE
JOIN STREET_LOCALITY SL ON AD.STREET_LOCALITY_PID=SL.STREET_LOCALITY_PID
LEFT JOIN STREET_SUFFIX_AUT SSA ON SL.STREET_SUFFIX_CODE=SSA.CODE
LEFT JOIN STREET_CLASS_AUT SCA ON SL.STREET_CLASS_CODE=SCA.CODE 
LEFT JOIN STREET_TYPE_AUT STA ON SL.STREET_TYPE_CODE=STA.CODE
JOIN LOCALITY L ON AD.LOCALITY_PID = L.LOCALITY_PID
JOIN ADDRESS_DEFAULT_GEOCODE ADG ON AD.ADDRESS_DETAIL_PID=ADG.ADDRESS_DETAIL_PID
LEFT JOIN GEOCODE_TYPE_AUT GTA ON ADG.GEOCODE_TYPE_CODE=GTA.CODE
LEFT JOIN GEOCODED_LEVEL_TYPE_AUT GLTA ON AD.LEVEL_GEOCODED_CODE=GLTA.CODE
JOIN STATE ST ON L.STATE_PID=ST.STATE_PID
LEFT JOIN mb_2011 MB ON AD.<join_column> = MB.<join_column>
WHERE 
AD.CONFIDENCE > -1;

Open in new window

0
 

Author Comment

by:gregfthompson
ID: 41839318
Thanks Sharath.
The .csv file contains these headings:
ADDRESS_MESH_BLOCK_2011_PID      
DATE_CREATED      
DATE_RETIRED      
ADDRESS_DETAIL_PID      
MB_MATCH_CODE      
MB_2011_PID

The table that is imported into postgres contains is called:
mb_2011
      Columns (2)
            mb_2011_pid
            date created
            date retired
            mb_2011_code
      Constraints (1):
-- Constraint: public.mb_2011_pk

-- ALTER TABLE public.mb_2011 DROP CONSTRAINT mb_2011_pk;

ALTER TABLE public.mb_2011
  ADD CONSTRAINT mb_2011_pk PRIMARY KEY(mb_2011_pid);


mb_match_code_aut
      Columns (3)
           code
           name
           description
       Constraints (1):
-- Constraint: public.mb_match_code_aut_pk

-- ALTER TABLE public.mb_match_code_aut DROP CONSTRAINT mb_match_code_aut_pk;

ALTER TABLE public.mb_match_code_aut
  ADD CONSTRAINT mb_match_code_aut_pk PRIMARY KEY(code);

I want to add these tables to the VIEW script but it shows an error:
       AD.MB_2011_PID as MB_2011_PID,
            MB.mb_2011_code
           
            FROM
           
            ADDRESS_DETAIL AD
            LEFT JOIN FLAT_TYPE_AUT FTA ON AD.FLAT_TYPE_CODE=FTA.CODE
            LEFT JOIN LEVEL_TYPE_AUT LTA ON AD.LEVEL_TYPE_CODE=LTA.CODE
            JOIN STREET_LOCALITY SL ON AD.STREET_LOCALITY_PID=SL.STREET_LOCALITY_PID
            LEFT JOIN STREET_SUFFIX_AUT SSA ON SL.STREET_SUFFIX_CODE=SSA.CODE
            LEFT JOIN STREET_CLASS_AUT SCA ON SL.STREET_CLASS_CODE=SCA.CODE
            LEFT JOIN STREET_TYPE_AUT STA ON SL.STREET_TYPE_CODE=STA.CODE
            JOIN LOCALITY L ON AD.LOCALITY_PID = L.LOCALITY_PID
            JOIN ADDRESS_DEFAULT_GEOCODE ADG ON AD.ADDRESS_DETAIL_PID=ADG.ADDRESS_DETAIL_PID
            LEFT JOIN GEOCODE_TYPE_AUT GTA ON ADG.GEOCODE_TYPE_CODE=GTA.CODE
            LEFT JOIN GEOCODED_LEVEL_TYPE_AUT GLTA ON AD.LEVEL_GEOCODED_CODE=GLTA.CODE
            JOIN STATE ST ON L.STATE_PID=ST.STATE_PID
            LEFT JOIN MB_2011 MB ON AD.MB_2011_PID = MB.ADDRESS_DETAIL_PID
            WHERE
            AD.CONFIDENCE > -1
            ERROR:  column ad.mb_2011_pid does not exist
            LINE 75: LEFT JOIN MB_2011 MB ON AD.MB_2011_PID = MB.ADDRESS_DETAIL_P...
                                             ^
            HINT:  Perhaps you meant to reference the column "mb.mb_2011_pid".
0
 

Author Comment

by:gregfthompson
ID: 41839320
It looks like it needs to use ADDRESS_DETAIL_PID column to match contents with contents of MB_2011_PID column, but ADDRESS_DETAIL_PID is not imported into the mb_2011 table.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 41843011
The HINT is correct. you used aliases in wrong order.

LEFT JOIN MB_2011 MB ON MB.MB_2011_PID = AD.ADDRESS_DETAIL_P...
0
 

Author Closing Comment

by:gregfthompson
ID: 41847501
Thanks heaps. Much appreciated.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now