Create View syntax problem
 

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.

Re: Create View syntax problem
 

I've solved it! for anyone elses info it was simply a combination of daft mistakes (see below).

I had not taken into account duplicate column names in the generated view (so I added aliases), a "time saving" copy and paste idea had resulted in the use of some incorrect table names, also I had not added AND operators when defining the joins in the WHERE clause. So the corrected statement is as follows:

CREATE VIEW LLPG AS
SELECT PR_BLPU.KEYVAL BLPU_KV, PR_BLPU.UPRN, PR_BLPU.MAP_EAST,
pr_BLPU.MAP_NORTH, PR_BLPU.LOGICAL_STATUS BLPU_STATUS,
PR_LPI.KEYVAL LPI_KV, PR_LPI.PKEYVAL LPI_PKV, PR_LPI.ST_KEYVAL LPI_SKV,
PR_LPI.ADDRESS, PR_LPI.CURRENCY_FLAG, PR_LPI.LOGICAL_STATUS LPI_STATUS,
PR_LPI.PAO_START_NO, PR_LPI.PAO_START_SFX, PR_LPI.PAO_END_NO,
PR_LPI.PAO_END_SFX, PR_LPI.PAO_DESC, PR_LPI.SAO_START_NO,
PR_LPI.SAO_START_SFX, PR_LPI.SAO_END_NO, PR_LPI.SAO_END_SFX,
PR_LPI.SAO_DESC, PR_LPI.POSTCODE, PR_LPI.POST_TOWN,
PR_LPI.OFFICIAL_ADDR,
PR_ST_DESC.KEYVAL DESC_KV, PR_ST_DESC.PKEYVAL DESC_PKV, PR_ST_DESC.STREET_NAME,
PR_ST_DESC.LOCALITY_NAME, PR_ST_DESC.TOWN_NAME,
PR_ST_DESC.COUNTY_NAME, PR_ST_DESC.TRADING_ESTATE,
PR_ST_DESC.DISTRICT_NAME, PR_ST_DESC.POSTAL_TOWN,
PR_ST_DESC.POSTAL_COUNTY,
PR_STREET.KEYVAL STREET_KV, PR_STREET.USRN
FROM PR_BLPU, PR_LPI, PR_ST_DESC, PR_STREET
WHERE
PR_BLPU.KEYVAL = PR_LPI.PKEYVAL
AND
PR_LPI.ST_KEYVAL = PR_ST_DESC.KEYVAL
AND
PR_ST_DESC.PKEYVAL = PR_STREET.KEYVAL;


I found a trial version of TOAD ( a simple SQL GUI application) really helpful in working out what was wrong.