Use column alias in the same element

Question

How can I use a column alias somewhere else in the same query? Is this possible in Oracle?

Example of using EMP_ID:

SELECT
    t1.DATE, t2.NAME, t1.ID,
    TO_NUMBER( SUBSTR( t1.NUMBER_ID, - 6)
      || TRIM( TO_CHAR( SUBSTR(EMP_ID, 3, 2), '00' ) ), '999999999999') AS CONTRACT,
    t2.ADDRESS,
    CASE WHEN SUBSTR(t2.COD_EMP, 0, 2) != 'PG' THEN 'PG00'
      || t2.COD_EMP ELSE t2.COD_EMP END AS EMP_ID
FROM
    TABLE_01 t1
    INNER JOIN TABLE_02 t2 .....

Answer 1

In standard SQL: all columns in a particular statement SELECTare evaluated as if "they are all evaluated in parallel (to allow some implementation to do just that).

Thus, you cannot depend on another column defined in the same sentence SELECT, since it has not yet been calculated.

Answer 2

, , order by:

. Oracle . AS . . order_by_clause .