Why ORA-00932: Inconsistent Types error?
When you are casting a varchar to a number you have to make sure this is possible for ALL values in this table. So in your case also for the values you think are filtered by "WHERE UPPER(NAME)!='XXX'".
The reason is the optimizer does not have to follow a specific order for the logic in your query. So it could do the casting and joining the tables frist and only after do the filtering specified in the inner query.
Changing the * to a specific field can change the execution plan the optimizer chooses, hence you could have the problem with one version and not the other.

You could use your own function to avoid this error:

create or replace function tonumberorzero(txt in varchar2) return number
 is
   retval number;
 begin
    return to_number(txt);
 exception
    when invalid_number then return 0;
 end;