Using COALESCE() in a query is breaking

No, the subquery itself must have an alias, as any aliases or table identifiers used inside the subquery are stripped off as the result set exits the subquery.  Maybe my example was confusing.  It would be more clear if you did this:

SELECT fname, lname, ORGS.EntityName dir, Child.EntityName div, Table3.personalid, COALESCE(Child.EntityName, ORGS.EntityName, 'N/A') org    
FROM personnel, (ORGS INNER JOIN ORGS Table2b ON ORGS.OrgID = Table2b.ParentID) Child, Table3

Actually, the more I think on it, that part of the FROM statement doesn't even look like a subquery.  A subquery should have a SELECT...FROM... syntax.  What you have just looks like a convoluted JOIN.  Here is how I visualize it (if I were writing the query as T-SQL for SQL Server ):

SELECT personnel.fname, personnel.lname, ORGS.EntityName AS dir, ORGS_DIV.EntityName AS div, Table3.personalid, COALESCE(ORGS_DIV.EntityName, ORGS.EntityName, 'N/A') AS org
FROM personnel 
     INNER JOIN ORGS ON personnel.OrgID = ORGS.OrgID
     INNER JOIN ORGS AS ORGS_Div ON ORGS.OrgID = ORGS_Div.ParentID
     INNER JOIN Table3 ON ..... {not sure what the common key is here}

I have no experience with Oracle, so maybe if you define table relationships elsewhere it automatically knows how to join those tables together without having to put in all the ON clauses.  Or do you have a WHERE clause that defines the associated columns?