oracle sql union order by

This is a certification question case A and case B, why it works in case B, but doesn't work in case A

case A (not working)
select col_a, col_b, 'b' from table_a
union
select col_a, col_b, 'a' from table_a
order by 'b';

case B (working)
select col_a, col_b, 'b' from table_a order by 'b';

You're sorting data by a string literal, 'a' which is as good as any other string literal, such as 'Littlefoot': both are useless, but are allowed:

SQL> select dname, 'a' from dept order by 'a';

DNAME          '
-------------- -
ACCOUNTING     a
RESEARCH       a
SALES          a
OPERATIONS     a

SQL> select dname, 'a' from dept order by 'Littlefoot';

DNAME          '
-------------- -
ACCOUNTING     a
RESEARCH       a
SALES          a
OPERATIONS     a

SQL>

order by clause - applied to unioned queries - works for the whole data set returned by that union:

SQL> select dname, 'a' from dept where deptno <= 20
  2  union
  3  select dname, 'b' from dept where deptno > 20
  4  order by dname;

DNAME          '
-------------- -
ACCOUNTING     a
OPERATIONS     b
RESEARCH       a
SALES          b

SQL>

As you already know, sorting by string literal won't work in this case:

SQL> select dname, 'a' from dept where deptno <= 20
  2  union
  3  select dname, 'b' from dept where deptno > 20
  4  order by 'Littlefoot';
order by 'Littlefoot'
         *
ERROR at line 4:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression


SQL>

That error literally means:

> You tried to execute a SELECT statement that included a ORDER BY clause that referenced a column number that did not correspond to a valid column in your SELECT list.

As you were already told, you could use an alias or column's position, or use that union as a subquery and then apply sorting by a string literal - in that case, it'll (again) work:

SQL> select *
  2  from (select dname, 'a' from dept where deptno <= 20
  3        union
  4        select dname, 'b' from dept where deptno > 20
  5       )
  6  order by 'Littlefoot';

DNAME          '
-------------- -
ACCOUNTING     a
OPERATIONS     b
RESEARCH       a
SALES          b

SQL>

That's useless (as it was before), but it is allowed.

One could hope that something like this would work ("hope" by means of implicit data conversion, where Oracle would convert '2' (a string) to 2 (a number) and sort by position), but - nope:

SQL> select dname, 'a' from dept where deptno <= 20
  2  union
  3  select dname, 'b' from dept where deptno > 20
  4  order by '2';
order by '2'
         *
ERROR at line 4:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Positional sorting works, of course:

SQL> select dname, 'a' from dept where deptno <= 20
  2  union
  3  select dname, 'b' from dept where deptno > 20
  4  order by 2;

DNAME          '
-------------- -
RESEARCH       a
ACCOUNTING     a
SALES          b
OPERATIONS     b

SQL>

Therefore, I suspect that it is the union that causes problems. Maybe it is about the fact that UNION returns DISTINCT data set, and - with it - ORDER BY clause can't refer columns that aren't part of the SELECT column list, literally.

'b' is a string literal and not an identifier for the column (as the identifier for the column is the quoted identifier "'B'").

You can either use the column number:

select col_a, col_b, 'b' from table_a union
select col_a, col_b, 'a' from table_a
order by 3;

or use a quoted identifier and convert the literal to upper-case:

select col_a, col_b, 'b' from table_a union
select col_a, col_b, 'a' from table_a
order by "'B'";

or give the column an alias and use that:

select col_a, col_b, 'b' AS b from table_a union
select col_a, col_b, 'a' from table_a
order by b;

See the SELECT documentation

> ### Set Operators: UNION, UNION ALL, INTERSECT, MINUS
>
> [...]
>
> #### order_by_clause
>
> Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
>
> [...]
>
> expr
>
> expr orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or materialized views in the FROM clause.
>
> [...]
>
> ### Restrictions on the ORDER BY Clause
>
> The following restrictions apply to the ORDER BY clause:
>
> - If you have specified the DISTINCT operator in this statement, then this clause cannot refer to columns unless they appear in the select list.

You are using UNION (and not UNION ALL) which is implicitly applying the DISTINCT operator so the restriction applies that you "refer to columns unless they appear in the select list." The identifier for the column in the select list is the quoted identifier "'B'" and not the string literal 'b' (and also not the string literal 'B') and you need to refer to it as that (or by the position in the select list or by an alias).

Note: Even without applying the additional restriction from the implicit DISTINCT, the base restriction for UNION queries is "The expression is based on columns in the select list or columns in the tables, views, or materialized views in the FROM clause" and the literal 'b' is not a column in the select list or from an object of the FROM clause so it would be valid if you used UNION ALL instead of UNION.