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