oracle: aliasing problem, why isn't recognizing it?

Ok guys, im having a real issue trying to understand the logic behind the aliasing in oracle. Here is the query:

select isbn,
       b.fname,
       b.lname
from   bookauthor a,
       author b,
      开发者_C百科 (select authorid auth
        from   bookauthor
        where  isbn = (select max(isbn) maxisbn
                       from   orderitems))
where  b.authorid = auth
       and isbn = maxisbn;  

for some reason, the dbms isn't recognizing maxisbn as an alias, but if i get rid of everything regarding maxisbn, the query runs and recognizes the alias "auth" just fine. Why is it seeing one but not the other?

---

maxisbn isn't the name of any column exposed by your derived table's SELECT list.

In order for this syntax to work you would need to add it as a column as below.

select isbn,
       b.fname,
       b.lname
from   bookauthor a,
       author b,
       (select authorid auth, isbn maxisbn
        from   bookauthor
        where  isbn = (select max(isbn) maxisbn
                       from   orderitems))
where  b.authorid = auth
       and isbn = maxisbn;  

NB: There is probably definitely a more efficient way of writing this query (you shouldn't need to access bookauthor twice) and I would always use explicit JOIN syntax. This is just to answer your specific question about why it doesn't work.

---

To take up Martin's question if this can be written more efficiently.

I think that the following will perform better and still return the same thing:

SELECT a.max_isbn,
       b.fname,
       b.lname
FROM  (SELECT authorid as max_authorid, 
              isbn as max_isbn
       FROM bookauthor
       WHERE isbn = (SELECT max(isbn) FROM orderitems)) a
  JOIN author b ON a.max_authorid = b.authorid AND a.max_isbn = b.isbn