Need help for SQL query (again)

Question

0.00/5 (No votes)

See more:

Hy guys.

I've been messing with this query for about a hour trying to find the way to display a chosen date that has been added with some month (from another table) and then displays the smallest value based on their parents. Now, code speak more than word, here it is:

SQL

SELECT   l.ID_JALURPOHON "ID Jalur Pohon",
            l.ID_RUASJALAN 
         || ' - '
         || (SELECT   N.NAMA_PANJANG
               FROM   NAMA_JALAN N
              WHERE   L.ID_RUASJALAN = N.ID_NAMAJALAN) /*l.ID_RUASJALAN works fine here*/
            "Nama Jalan",
         (SELECT   da
            FROM   (  SELECT   ADD_MONTHS (D.TGL_PERABASAN, J.PERIODE_RABAS) da
                        FROM   ADETIL_POHON D, Jenis_POHON J
                       WHERE   d.ID_JENISPOHON = j.ID_JENISPOHON
                               AND d.ID_JALURPOHON = l.ID_JALURPOHON /*l.ID_JALURPOHON not recognized here*/
                    ORDER BY   da ASC)
           WHERE   ROWNUM <= 1)
            "Tanggal Perabasan"
  FROM   APOHON L

Any idea of what may be the cause? Or the workaround?

Posted 22-Sep-11 0:00am

Firo Atrum Ventus

Updated 22-Sep-11 16:24pm

v4

Add a Solution

Comments

Jörgen Andersson 24-Sep-11 13:26pm

What's the actual error?

Firo Atrum Ventus 25-Sep-11 22:04pm

ORA-00904: "L"."ID_JALURPOHON": invalid identifier

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2011-09-26T01:07:00

I assume you're using Oracle 9i or earlier, because it can only reference a column from a table referred to in the parent statement. Not two levels up. (From version 10 and forward it's unlimited.
I would suggest you use a join instead of a subquery.

Like something similar to this

SQL

SELECT   l.ID_JALURPOHON "ID Jalur Pohon",
         l.ID_RUASJALAN
         || ' - '
         || (SELECT   N.NAMA_PANJANG
               FROM   NAMA_JALAN N
              WHERE   L.ID_RUASJALAN = N.ID_NAMAJALAN)"Nama Jalan",
         m.da "Tanggal Perabasan"
  FROM   APOHON L, (  SELECT   ADD_MONTHS (D.TGL_PERABASAN, J.PERIODE_RABAS) da,
                               d.ID_JALURPOHON 
                        FROM   ADETIL_POHON D, Jenis_POHON J
                       WHERE   d.ID_JENISPOHON = j.ID_JENISPOHON
                    ORDER BY   da ASC) M
  WHERE   ROWNUM <= 1 
    AND   m.ID_JALURPOHON = l.ID_JALURPOHON