我正在尝试创建以下视图:
CREATE OR REPLACE VIEW AlbumDistribution AS
SELECT Album.Album_ID,
Album.title,
HasTrack.tracked,
FinishedTrack.released_title,
SUBSTR(Album.Album_ID, -1) is_distributed_as
FROM Album A
JOIN HasTrack HT
ON HT.Album_ID = A.Album_ID
JOIN FinishedTrack FT
ON HasTrack.OriginatesFrom = FT.OriginatesFrom
AND HasTrack.tracked = FT.version;
但是我得到了ORA-00904
错误:
ERROR at line 6:
ORA-00904: "HASTRACK"."TRACKED": invalid identifier
我觉得这很令人困惑,因为我之前引用了HasTrack.track,没有错误。如果我更改语句的顺序,将HasTrack.OoriginatesFrom=FT.OoriginatsFrom放在最后,那么我会得到相同的错误,但对于HasTrack.OOriginatesFrom.
您可以为这个表和其他表定义一个别名。您需要在整个查询过程中使用别名:
CREATE OR REPLACE VIEW AlbumDistribution AS
SELECT A.Album_ID, A.title, HT.tracked,
FT.released_title, SUBSTR(A.Album_ID, -1) is_distributed_as
FROM Album A JOIN
HasTrack HT
ON HT.Album_ID = A.Album_ID JOIN
FinishedTrack FT
ON HT.OriginatesFrom = FT.OriginatesFrom AND
HT.tracked = FT.version;