title: UNION ALL, ORDER BY at the end, invalid identifier, ascending, error message
body: <p>I am getting another error message. Please see the below error message. Here I am doing a UNION ALL and I can confirm that this query works without the ORDER BY at the end. However, I do need to order by that column at the end. So, I am not sure how to fix this issue. I even tried removing the second query a. to a1. so they have different aliases. I now know to put ORDER BY at the end of the code when doing a UNION ALL but did not expect to get invalid identifier. </p>
  
  <p>Error Message </p>
  
  <pre><code>ORA-00904: "A1"."ID1": invalid identifier
  Error at Line: 1 Column: 678
  </code></pre>
  
  <p>Code</p>
  
  <pre><code>select a.ID2, a.ID1, cnt, a.*, b.*
  from (
  select t.*, 
  count(distinct ID2) over(partition by ID1) cnt 
  from TABLE1 t) a
  , TABLE2 b 
  where a.cnt &gt; 1
  and a.ID2=b.ID2 (+)
  and (b.STATUS in ('A','L','P','S') 
  or b.DATE1 &gt;= TO_DATE('2018-01-01','YYYY-MM-DD'))
  union all
  select a1.ID2, a1.ID1, cnt, a1.*, j.*
  from (
  select t.*, 
  count(distinct ID2) over(partition by ID1) cnt 
  from TABLE1 t) a1
  inner join TABLE2 j
  on a1.ID2=j.ID2
  inner join SCHEMA1.TABLE3 d
  on j.ALTER_ID2=d.ALTER_ID2
  where a1.cnt &gt; 1
  order by a.ID1 asc, a1.ID1 asc
  </code></pre>