CODEWITHSUNDEEP
sqloracle-databaseviewora-00904
Djakninn
Djakninn

Reputation: 227

ORA-00904 on join

I am trying to create the following view:

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;

but I get the ORA-00904 error:

 ERROR at line 6:
 ORA-00904: "HASTRACK"."TRACKED": invalid identifier

Which I find very confusing, as I reference HasTrack.tracked before and there's no error. If I change the order of the statements, putting HasTrack.OriginatesFrom = FT.OriginatesFrom last then I get the same error but for HasTrack.OriginatesFrom.

Upvotes: 0

Views: 179

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

You define an alias for this and other tables. You need to use the alias throughout the query:

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;

Upvotes: 2

Related Questions