Oracle ORA:00904: Subquery in LEFT JOIN

Question

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.

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 FIRST_VALUE analytic functions in my select values. We're on 10g.)

The error:

Error(111,79): PL/SQL: ORA-00904: "VT"."MAIL_TO_ADDRESS_NUMBER": invalid identifier

The proc:

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;

Answer 1

You are trying to do a lateral join. You cannot use an external table alias in the from clause. In general, the work-around is to use aggregation:

  SELECT
    ...
  FROM vehicle_transaction vt INNER JOIN
       registration_transaction reg
       ON vt.transaction_id = reg.transaction_id LEFT OUTER JOIN
       (SELECT laddt2.address_number, MIN(laddt2.address) as address
        FROM lien_address_transaction laddt2
        WHERE vt.mail_to_address_number IS NOT NULL AND
        GROUP BY laddt2.address_number
       ) laddt
       ON laddt.address_number = vt.mail_to_address_number
  WHERE vt.transaction_id = p_TransactionId;