Q:

How to access the alias name in below query

Below is my query, I want to access the UNQKEY value I can use it in order by but I want to use it in where condition. Is there any want to use the UNQKEY as part of where condition?
SELECT distinct TOCID,'UNQKEY' || r UNQKEY  ,TOCNAME,
  decode( r, 1, UNQKEY1, 2, UNQKEY2, 3, UNQKEY3, 4, UNQKEY4 ,   5, UNQKEY5) AS 
  UNQKEY_VALUE
FROM TOC, RULE RL,
  (select rownum r
    from all_objects
    where rownum <=5) 
WHERE TOCID=1210 
ORDER BY UNQKEY

A:

If you want to use it in a where clause, then use a subquery or CTE:
select *
from (select distinct TOCID, 'UNQKEY' || r as UNQKEY , TOCNAME,
             decode( r, 1, UNQKEY1, 2, UNQKEY2, 3, UNQKEY3, 4, UNQKEY4 ,   5, UNQKEY5) AS UNQKEY_VALUE
      from TOC, RULE RL,
           (select rownum r
            from all_objects
            where rownum <=5
           )
      ) t
WHERE TOCID = 1210 and UNQKEY like '%%'
order by UNQKEY;

