Use column alias in same select [duplicate]

Question

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

Example 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

Per standard SQL: All columns in any particular SELECT clause are computed "as if" they're all being computed in parallel (to allow some implementation to do precisely that).

As such, you're not allowed to depend on another column defined within the same SELECT clause since it's value has not yet been computed.

Answer 2

If you check the documentation, you will see that you are only allowed to use it in the order by clause:

Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.