[Database][Oracle] Union with Order By

Deskripsi
      sometimes we get confused at the time of the union query with sort order and always ended with "invalid identifier" , where's the problem ?? the problem oracle doesn't know about the field_name. what the solution ?? to identify the field union when using order by just use column index. 

Howto
case :
1. i have the table with different column format, but there's 2 same field and i want to union
2. i want order by y and z column

Column table A : x,y,z
Column table B : s,y,z

the solution
select *
from
(
select x,y,z
from A
union all -- or union
selecy null,y,z -- null means fake column
from B
) data --aliases
order by 2,3 ;
-- 2 and 3 is index column from left : x(1) , y(2) , z(3) .


if you want still use field_name , add it with alias (recommended)
select *
from
(
select x as x_one,
         y as y_two,
         z as z_three
from A
union all -- or union
selecy null,y,z -- null means fake column
from B
) data --aliases
order by data.y_two,data.z_three ;