Use an Alias in Where Clause Subquery

Question

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 <> 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.PANGKAT >= 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 >= 12
        and sub.job_order = 1
        and sub.kualifikasi > = 2
order by sub.hari desc

Answer 1

@haki has identified why this error occurs. You can't use an alias (KODE) defined in the SELECT list, in the definition of another column in that list. You have to use the full reference instead (ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI).

But the solution provided, does not work I think because of the double nesting. You can reference a column in a inline subquery but not in a derived table inside an inline subquery.

This would require either using ROW_NUMBER() and encapsulating the whole query ina derived table or turning the inline subquery into a LEFT JOIN. But since you have ROWNUM=1 and ORDER BY TMT_JABATAN desc and you are selecting only TMT_JABATAN in that inline subquery, you can use MAX(TMT_JABATAN) instead and one-level nesting:

SELECT
    sp.ID_PEGAWAI AS KODE,
    sp.NAMA,
    sp.NIP,
    sej.JABATAN,
    skgp.GOLONGAN,
    skgp.PANGKAT,
    ( SELECT CONCAT(TO_CHAR(abs(sysdate - TO_DATE(MAX(sj.TMT_JABATAN)))/360,'9,999,999.9'),
                    ' TAHUN') 
      FROM SIMPEG_JABATAN  sj
      WHERE sj.ID_PEGAWAI = sp.ID_PEGAWAI  
    ) AS MASA_KERJA
FROM 
        ANGGARAN.SIMPEG_PEGAWAI  sp
    INNER JOIN ANGGARAN.SIMPEG_ESELON_JABATAN   sej
        ON sp.ESELON_JABATAN = sej.ID_ESELON_JABATAN
    INNER JOIN ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT  skgp
        ON sp.PANGKAT = skgp.ID_GOLONGAN_PANGKAT
WHERE
    sp.ST_AKTIF = 1 AND
    sp.ESELON2 <> 1 AND
    sp.PANGKAT >= 12 AND 
    sej.STATUS = 1 AND
    sej.ID_ESELON2 = 2
ORDER BY
    skgp.SORT DESC ;

Answer 2

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 <> 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.PANGKAT >= 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

1. from
2. where
3. group by
4. having
5. select

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