ORA-01799: column cannot be externally joined to a subquery

Question

I have a select:

SELECT DISTINCT a.account_num
FROM   (SELECT account_num,
               Max(effective_dtm) EFFECTIVE_DTM
        FROM   accountstatus
        GROUP  BY account_num) a
       join accountstatus b
         ON b.account_num = a.account_num
            AND b.effective_dtm = a.effective_dtm
            AND b.account_status = 'OK'
       left join (SELECT account_num,
                         Max(bill_seq) bill_seq
                  FROM   ert_invoice
                  GROUP  BY account_num) last_bills
              ON last_bills.account_num = a.account_num
       join ert_invoice inv
         ON inv.account_num = last_bills.account_num
            AND inv.bill_seq = last_bills.bill_seq
            AND inv.suppress_status = 3
            AND inv.bill_type_id = 1
       left join dispute d
              ON d.account_num = a.account_num
                 AND d.created_dtm >= (SELECT Trunc(SYSDATE, 'mm')
                                       FROM   dual)
                 AND d.dispute_status = 'P'
       left join adjustment ad
              ON ad.account_num = a.account_num
                 AND ad.created_dtm >= (SELECT Trunc(SYSDATE, 'mm')
                                        FROM   dual)
                 AND ad.adjustment_status IN ( '1', '3' )
       left join accountattributes aa
              ON aa.account_num = a.account_num
                 AND aa.account_sign IN ( 'PRI', 'TST' )
WHERE  d.account_num IS NULL
       AND ad.account_num IS NULL
       AND aa.account_num IS NULL; 

When creating a VIEW in the Oracle database, an error occurs: ORA-01799: the column cannot be externally connected to the subquery

As I understand it, the problem is in the line:

FROM (SELECT ACCOUNT_NUM, max(EFFECTIVE_DTM) EFFECTIVE_DTM FROM ACCOUNTSTATUS group by ACCOUNT_NUM) a

How can I fix it without changing the principle of operation?

Answer 1

Oracle do not allow subquery in JOIN condition.

In your case, subqueries are not even needed.

Replace following subqueries in your query:

(SELECT Trunc(SYSDATE, 'mm') FROM   dual)

With this expression:

Trunc(SYSDATE, 'mm')

From all three place