Preciso mostrar alguns campos de outra tabela no Oracle. Aqui está minha consulta:
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
O resultado que obtive:
[Err] ORA-00904: "KODE": invalid identifier
KODEvem de query ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODEe usado para esta consulta:
(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
Eu sinto falta de alguma coisa? Ou poderia estar usando um alias na subquery errado na whereclausula no Oracle?
editado: Resolvi este problema, obrigado a todos, aqui está:
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
