CODEWITHSUNDEEP
oracle10galias
Khairu Aqsara
Khairu Aqsara

Reputation: 1310

Use an Alias in Where Clause Subquery in Oracle

i need to show some field 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

result i got

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

the 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

that i miss something ? or that could be worogn using an alias in subquery where clause in oracle database ?

Upvotes: 0

Views: 7672

Answers (3)

lunr
lunr

Reputation: 5269

You can use an identifier defined in an external query in only one level deep queries. You have to rethink your strategy. My suggestion is to remove the subquery from the select list and put it in the FROM clause. And add another rownumber column like this:

(SELECT 
    ID_PEGAWAI,
    CONCAT(TO_CHAR(abs(sysdate - TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN') MASA_KERJA, 
    ROW_NUMBER() OVER (PARTITION BY ID_PEGAWAI ORDER BY TMT_JABATAN DESC) rownumber
FROM SIMPEG_JABATAN) xxx

And join like:

ON ANGGARAN.SIMPEG_PEGAWAI = xxx.ID_PEGAWAI

Then in the where clause you can do simply:

WHERE
     ....
     AND xxx.rownumber = 1

Complete 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
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
    INNER JOIN (
      SELECT 
        ID_PEGAWAI,
        CONCAT(TO_CHAR(abs(sysdate - TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN') MASA_KERJA, 
        ROW_NUMBER() OVER (PARTITION BY ID_PEGAWAI ORDER BY TMT_JABATAN DESC) rownumber
      FROM SIMPEG_JABATAN
    ) xxx
      ON ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI = xxx.ID_PEGAWAI
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 AND
  xxx.rownumber = 1
ORDER BY ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.SORT DESC

Upvotes: 1

whatsupbros
whatsupbros

Reputation: 802

You need to assign an alias in the level below to use it in a query (I haven't checked the syntax and workability of your query, just changed the part which is essential to answer your question):

SELECT
TMP.KODE,
TMP.NAMA,
TMP.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=TMP.KODE ORDER BY TMT_JABATAN desc)
    WHERE ROWNUM = 1) AS MASA_KERJA
FROM 
(SELECT ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE, ANGGARAN.SIMPEG_PEGAWAI.* FROM ANGGARAN.SIMPEG_PEGAWAI) TMP
    INNER JOIN ANGGARAN.SIMPEG_ESELON_JABATAN 
        ON TMP.ESELON_JABATAN = ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON_JABATAN
    INNER JOIN ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT 
        ON TMP.PANGKAT = ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.ID_GOLONGAN_PANGKAT
WHERE
    TMP.ST_AKTIF = 1 AND
    TMP.ESELON2 <> 1 AND
    TMP.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

Upvotes: 0

Thilo
Thilo

Reputation: 262504

Oracle does not support columns aliases in WHERE clauses (or similar situations like here). You have to name the column again (by its original name).

 select dummy as kode from dual where kode = 'X'

 >  ORA-00904: "KODE": invalid identifier

Upvotes: 0

Related Questions