WITH CLAUSE IN QUERY

WITH TEMP_TAB AS
2 (SELECT 'ABC' Col_1 FROM DUAL
3 UNION
4 SELECT 'DEF' FROM DUAL
5 UNION
6 SELECT 'GHI' FROM DUAL
7 )
8 SELECT Col_1 FROM TEMP_TAB
9* ORDER BY Col_1
10 ;

COL
---
ABC
DEF
GHI

3 rows selected.

SQL> ed
Wrote file afiedt.buf

1 WITH TEMP_TAB AS
2 (SELECT 'ABC' Col_1 FROM DUAL
3 UNION
4 SELECT 'DEF' FROM DUAL
5 UNION
6 SELECT 'GHI' FROM DUAL
7 )
8 SELECT
9 DECODE(COL_1, 'ABC', 2, 'DEF', 3, 'GHI', 1, 999)
10 SORT_1, COL_1 FROM TEMP_TAB
11* ORDER BY SORT_1
12 ;

SORT_1 COL
---------- ---
1 GHI
2 ABC
3 DEF