我需要显示 Oracle 中另一个表中的一些字段。这是我的查询：
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
我得到的结果：
[Err] ORA-00904: "KODE": invalid identifier
KODE来自查询ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE，并用于此查询：
(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
我错过了什么吗？where或者可能在 Oracle的子句中使用错误的子查询别名？
编辑：我已经解决了这个问题，谢谢大家，这里是：
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
