title: Oracle ORA:00904: Subquery in LEFT JOIN
body: <p>I'm aware of the subquery limitations of Oracle's ANSI SQL setup. You can't use an identifier in a subquery that is declared more than one level deep.</p>
  
  <p>I'm attempting the following query, which as far as I can see is only one level deep, but I'm getting this error. Does this not work for table joins? (I've truncated the procedure somewhat, but the problem should be clear. Additionally, if it means anything, I'm using <code>FIRST_VALUE</code> analytic functions in my select values. We're on 10g.)</p>
  
  <p>The error: </p>
  
  <pre><code>Error(111,79): PL/SQL: ORA-00904: "VT"."MAIL_TO_ADDRESS_NUMBER": invalid identifier
  </code></pre>
  
  <p>The proc:</p>
  
  <pre><code>PROCEDURE MYPROCEDURE (
    p_TransactionId IN NUMBER,
    p_Cursor_Out OUT SYS_REFCURSOR
  )
  AS
  BEGIN
    OPEN p_Cursor_Out FOR
    SELECT
      ...
    FROM vehicle_transaction vt
    INNER JOIN registration_transaction reg ON vt.transaction_id = reg.transaction_id
    /* The problem is here */
    LEFT OUTER JOIN (
      SELECT
        laddt2.address
      FROM lien_address_transaction laddt2
      WHERE vt.mail_to_address_number IS NOT NULL AND laddt2.address_number = vt.mail_to_address_number
    ) laddt
    ON (laddt2.address_number = vt.mail_to_address_number)
    WHERE vt.transaction_id = p_TransactionId;
  
  END MYPROCEDURE;
  </code></pre>