Re: why can't I create this VIEW?

Since you aliased the table, you need to use the alias in the select clause. Try a.ucan_station_id.

Russ

edwards_at_garland.dnr.state.sc.us wrote:

>SQL> CREATE VIEW climate.stations as
> select nws_stations.ucan_station_id,station_name,nws_station_id from
> ucan_stations a,nws_stations b
> where a.ucan_station_id = b.ucan_station_id;

>select nws_stations.ucan_station_id,station_name,nws_station_id from
> *
>ERROR at line 2:
>ORA-00904: invalid column name
 

>ORACLE tells me the column 'ucan_station_id' doesn't exist!
>but a DESCRIBE shows that the column exists!!
>what wrong with my syntax?
>(I own both tables and have permission to create a view)

>SQL> desc nws_stations;
> Name Null? Type
> ------------------------------- -------- ----
> UCAN_STATION_ID NOT NULL NUMBER(10)
> NWS_STATION_ID VARCHAR2(5)
 
>SQL> desc ucan_stations;
> Name Null? Type
> ------------------------------- -------- ----
> UCAN_STATION_ID NOT NULL NUMBER(10)
> STATION_SEQUENCE NOT NULL NUMBER(2)
> STATION_NAME VARCHAR2(20)
> STATE_FIPS_ID NUMBER(2)
> BEGIN_DATE DATE
> END_DATE DATE
> LATITUDE NUMBER(15,10)
> LONGITUDE NUMBER(16,10)
> ELEVATION NUMBER(6,1)
> TIME_ZONE VARCHAR2(3)
> COUNTY VARCHAR2(20)
> RELOCATION_INFO VARCHAR2(12)
> HOME_RCC VARCHAR2(5)
> BACKUP_RCC VARCHAR2(5)
> STATION_STATUS CHAR(1)
> REMARKS VARCHAR2(180)
 
>SQL>
Received on Sat Jan 18 1997 - 00:00:00 CST