Order by clause in an union set operation

[signalsys]

If i use an alias in the order by clause, error appears:
SQL>select 'sing' as mydream, 3 a_dummy from dual
union (select 'I''d like to teach',1 from dual
union select 'the world to',2 from dual) order by a_dummy;

ERROR at line 3:
ORA-00904: "A_DUMMY": invalid identifier

When i use a position number for the order by clause,it works well:

SQL>select 'sing' as mydream, 3 a_dummy from dual
union (select 'I''d like to teach',1 from dual
union select 'the world to',2 from dual) order by 2;

MYDREAM A_DUMMY
---------------------------------- ----------
I'd like to teach 1
the world to 2
sing 3

What cause this problem ?

 

[taupirho]

The "problem" is that Oracle does not allow you to use an alias in the order by clause - plain and simple.

 

[stefanhei]

Oracle has no problems with aliases in order by, but the parser doesn't know, that the second column in your second select should be named a_dummy.

  1  select 'sing' as mydream, 3 a_dummy from dual
  2  union (select 'I''d like to teach',1 a_dummy from dual
  3* union select 'the world to',2 from dual) order by a_dummy

MYDREAM              A_DUMMY
----------------- ----------
I'd like to teach          1
the world to               2
sing                       3

Simply name the column in your second select and the order by will work.

Stefan