Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
i have tried that, but the result is same, is there any way to use an alias in subquery where clause in oralce ? –  wenkhairu May 16 '13 at 8:46
1  
Please, don't update the title or the other post's parts that way (the word "Solved"). If you have accepted the correct answer, the post is specifically formatted on the site so that everyone knows without digging into the question that you've found the solution. Thanks. –  Yasir Arsanukaev May 16 '13 at 15:10
add comment

2 Answers

up vote 1 down vote accepted

@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 ;
share|improve this answer
    
thanks that work nicely dude, i have solved this one before yours, but i appriciate your answer because it works tu, i have update my code to working one, thanks dude –  wenkhairu May 16 '13 at 14:59
    
I think it's better if you posted your solution as an answer. (and remove it from the question) –  ypercube May 16 '13 at 15:01
    
how i do that ? i have update my question to solved one –  wenkhairu May 16 '13 at 15:03
    
You add the code (that solves your problem) in the "Your Answer" area, below. –  ypercube May 16 '13 at 15:07
    
Sorry, there's an "Answer Your Question" button for you to do that. –  ypercube May 16 '13 at 17:08
add comment

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.

share|improve this answer
    
The explanation of why the error occurs is correct. I'm not sure if your proposal is valid syntax though. –  ypercube May 16 '13 at 11:46
    
i cant check the syntax but all i did was to change the alias KODE in the inner query to ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI. is it not working ? –  haki May 16 '13 at 11:52
    
I'm not sure because of the double nesting. Can you check? –  ypercube May 16 '13 at 11:53
    
you can reference any column from the outer query at any level of sub query. this should not be a problem. do you get an syntax error when you try to execute the query ? –  haki May 16 '13 at 11:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.