Apologies if this is a simple query, I only get to use Oracle on the odd occasion so I tend to get a bit rusty. At present I am attempting to create a view that gathers data from 4 tables, this information will then be queried by an external application.
I have been using the following statement:
CREATE VIEW LLPG AS
SELECT UNI72LIVE_BLPU.KEYVAL, UNI72LIVE_BLPU.UPRN, UNI72LIVE_BLPU.MAP_EAST,
UNI72LIVE_BLPU.MAP_NORTH, UNI72LIVE_BLPU.LOGICAL_STATUS,
UNI72LIVE_PR_LPI.KEYVAL, UNI72LIVE_PR_LPI.PKEYVAL, UNI72LIVE_PR_LPI.ST_KEYVAL,
UNI72LIVE_PR_LPI.ADDRESS, UNI72LIVE_PR_LPI.CURRENCY_FLAG, UNI72LIVE_PR_LPI.LOGICAL_STATUS,
UNI72LIVE_PR_LPI.PAO_START_NO, UNI72LIVE_PR_LPI.PAO_START_SFX, UNI72LIVE_PR_LPI.PAO_END_NO,
UNI72LIVE_PR_LPI.PAO_END_SFX, UNI72LIVE_PR_LPI.PAO_DESC, UNI72LIVE_PR_LPI.SAO_START_NO,
UNI72LIVE_PR_LPI.SAO_START_SFX, UNI72LIVE_PR_LPI.SAO_END_NO, UNI72LIVE_PR_LPI.SAO_END_SFX,
UNI72LIVE_PR_LPI.SAO_DESC, UNI72LIVE_PR_LPI.POSTCODE, UNI72LIVE_PR_LPI.POST_TOWN,
UNI72LIVE_PR_LPI.OFFICIAL_ADDR,
UNI72LIVE_PR_ST_DESC.KEYVAL, UNI72LIVE_PR_ST_DESC.PKEYVAL, UNI72LIVE_PR_ST_DESC.STREET_NAME,
UNI72LIVE_PR_ST_DESC.LOCALITY_NAME, UNI72LIVE_PR_ST_DESC.TOWN_NAME,
UNI72LIVE_PR_ST_DESC.COUNTY_NAME, UNI72LIVE_PR_ST_DESC.TRADING_ESTATE,
UNI72LIVE_PR_ST_DESC.DISTRICT_NAME, UNI72LIVE_PR_ST_DESC.POSTAL_TOWN,
UNI72LIVE_PR_ST_DESC.POSTAL_COUNTY,
UNI72LIVE_PR_STREET.KEYVAL, UNI72LIVE_PR_STREET.USRN
FROM UNI72LIVE_BLPU, UNI72LIVE_PR_LPI, UNI72LIVE_PR_ST_DESC, UNI72LIVE_PR_STREET
WHERE
UNI72LIVE_BLPU.KEYVAL = UNI72LIVE_PR_LPI.PKEYVAL
UNI72LIVE_PR_LPI.ST_KEYVAL = UNI72LIVE_PR_ST_DESC.KEYVAL
UNI72LIVE_PR_ST_DESC.PKEYVAL = UNI72LIVE_PR_STREET.KEYVAL;
When this script is executed I receive errors such as ORA-00936 and ORA-00942. From the Oracle documentation I think I am using the create view correctly, but I have only seen examples with single joins. Is it possible to link more than 1 table together in a view?
If anyone can spot a mistake or provide any advice it would be a real help.
Many thanks,
Martin.