Use an Alias in Where Clause Subquery I need to show some fields from another table in Oracle. Here is my query:

SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
ANGGARAN.SIMPEG_ESELON_JABATAN.JABATAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.GOLONGAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.PANGKAT,
    (SELECT * FROM (SELECT CONCAT(TO_CHAR(abs(sysdate
    - TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN') 
       FROM SIMPEG_JABATAN where ID_PEGAWAI=KODE ORDER BY TMT_JABATAN desc)
    WHERE ROWNUM = 1) AS MASA_KERJA
FROM 
ANGGARAN.SIMPEG_PEGAWAI
    INNER JOIN ANGGARAN.SIMPEG_ESELON_JABATAN 
        ON ANGGARAN.SIMPEG_PEGAWAI.ESELON_JABATAN = 
        ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON_JABATAN
    INNER JOIN ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT 
        ON ANGGARAN.SIMPEG_PEGAWAI.PANGKAT = 
        ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.ID_GOLONGAN_PANGKAT
WHERE
    ANGGARAN.SIMPEG_PEGAWAI.ST_AKTIF = 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.ESELON2 &lt;&gt; 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.PANGKAT &gt;= 12 AND 
    ANGGARAN.SIMPEG_ESELON_JABATAN.STATUS = 1 AND
    ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON2=2
ORDER BY
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.SORT DESC


The result I got:

[Err] ORA-00904: "KODE": invalid identifier


KODE come from query ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE, and used for this query :

(SELECT *
        FROM (SELECT CONCAT(TO_CHAR(abs(sysdate - 
    TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN') 
            FROM SIMPEG_JABATAN where ID_PEGAWAI=KODE ORDER BY TMT_JABATAN desc)
WHERE ROWNUM = 1) AS MASA_KERJA


Do I miss something? Or could be using an alias in subquery wrong in the where clause in Oracle?

edited:
I have solved this problem, thanks for every one, here it is:

select
  e.nama as nama,
  e.nip  as nip,
    f.golongan as golongan,
    f.pangkat as pangkat_golongan,  
    g.jabatan as jabatan_akhir,
    sub.tmt_jabatan as lama_menjabat,
    sub.kualifikasi as kualifikasi,
    sub.hari as hari_kerja
from 
  simpeg_pegawai e,
  (
    select
      j.id_pegawai as id_pegawai,
            extract(year from (sysdate - j.tmt_jabatan) year to month) || ' Tahun ' ||
            extract(month from (sysdate - j.tmt_jabatan) year to month) || ' Bulan ' 
            tmt_jabatan,
            extract(year from (sysdate - j.tmt_jabatan) year to month) kualifikasi,
            trunc((((86400*(sysdate-j.tmt_jabatan))/60)/60)/24) as hari,
      row_number()
        over (
          partition by j.id_pegawai
          order by     j.tmt_jabatan desc
        ) as job_order
    from
      simpeg_jabatan j
  ) sub,simpeg_kode_golongan_pangkat f, simpeg_eselon_jabatan g

where 
        sub.id_pegawai = e.id_pegawai
        and e.pangkat = f.id_golongan_pangkat
        and e.eselon_jabatan = g.id_eselon_jabatan
        and g.id_eselon2 = 2
        and g.status = 1
        and e.st_aktif=1
        and e.eselon2=2
        and e.pangkat &gt;= 12
        and sub.job_order = 1
        and sub.kualifikasi &gt; = 2
order by sub.hari desc
you cant use alias from the wraping query in the a sub query
change it this way 

SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
ANGGARAN.SIMPEG_ESELON_JABATAN.JABATAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.GOLONGAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.PANGKAT,
    (SELECT *
            FROM (SELECT CONCAT(TO_CHAR(abs(sysdate - TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN') 
                FROM SIMPEG_JABATAN where ID_PEGAWAI=ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI  ORDER BY TMT_JABATAN desc)
    WHERE ROWNUM = 1) AS MASA_KERJA
FROM 
ANGGARAN.SIMPEG_PEGAWAI
    INNER JOIN ANGGARAN.SIMPEG_ESELON_JABATAN 
        ON ANGGARAN.SIMPEG_PEGAWAI.ESELON_JABATAN = ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON_JABATAN
    INNER JOIN ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT 
        ON ANGGARAN.SIMPEG_PEGAWAI.PANGKAT = ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.ID_GOLONGAN_PANGKAT
WHERE
    ANGGARAN.SIMPEG_PEGAWAI.ST_AKTIF = 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.ESELON2 &lt;&gt; 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.PANGKAT &gt;= 12 AND 
    ANGGARAN.SIMPEG_ESELON_JABATAN.STATUS = 1 AND
    ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON2=2
ORDER BY
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.SORT DESC


remember, sql engine process a query in the following order 


from 
where
group by 
having 
select


so, when you execute the inner query the sql engine does no know the aliad KODE yet.