Início / dba / Perguntas / 42337
Accepted
Khairu Aqsara
Asked: 2013-05-17 00:42:32 +0800 CST

Use um alias na subconsulta da cláusula Where

  • 772

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
oracle subquery

2 respostas

  1. você não pode usar o alias da consulta de quebra automática na subconsulta, altere desta forma

    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

    lembre-se, o mecanismo SQL processa uma consulta na seguinte ordem

    1. a partir de
    2. Onde
    3. agrupar por
    4. tendo
    5. selecionar

    então, quando você executa a consulta interna, o mecanismo sql ainda não conhece o aliad KODE.

    • 1
  2. Best Answer

    @haki identificou por que esse erro ocorre. Você não pode usar um alias ( KODE) definido na SELECTlista, na definição de outra coluna dessa lista. Você deve usar a referência completa ( ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI).

    Mas a solução fornecida não funciona, acho, por causa do aninhamento duplo. Você pode referenciar uma coluna em uma subconsulta embutida, mas não em uma tabela derivada dentro de uma subconsulta embutida.

    Isso exigiria usar ROW_NUMBER()e encapsular toda a consulta em uma tabela derivada ou transformar a subconsulta embutida em uma LEFT JOIN.. Mas como você tem ROWNUM=1e ORDER BY TMT_JABATAN descestá selecionando apenas TMT_JABATANnessa subconsulta embutida, você pode usar MAX(TMT_JABATAN)o aninhamento em um nível:

    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 ;
    • 1

relate perguntas