Unable to select data from another joined table

debugcn Published at Dev

Hatik

I have some tables with huge data, so I try to use that data to create a kind of a report, based on some conditions using `LEFT JOIN',

SELECT SUBSIDIARY, MAIN_ACCOUNT, AMOUNT ... FROM (
...
LEFT JOIN (SELECT MAIN_ACCOUNT, ACCOUNT_NO AS SUBSIDIARY FROM TABLE1 WHERE STATUS = 'A') K
ON MAIN_ACCOUNT = K.MAIN_ACCOUNT
LEFT JOIN (SELECT SUM(AMOUNT) AS AMOUNT, ACCOUNT_NO, FROM TABLE2 WHERE ACCOUNT_NO = K.ACCOUNT_NO GROUP_BY ACCOUNT_NO) L
ON SUBSIDIARY = L.ACCOUNT_NO --this is where i got into a problem
...
);

The problem is that I cannot access the K.ACCOUNT_NO from another join I have tried using WHERE ACCOUNT_NO = SUBSIDIARY since I select in the main SELECT. Does it mean that I cannot access previously queried data from another join?

Error I get:

ORA-00904: "SUBSIDIARY": invalid identifier

if I use k.subsidiary or k.account_no

ORA-00904: "k.SUBSIDIARY": invalid identifier
ORA-00904: "k.account_no": invalid identifier

Tien Nguyen Ngoc

You can not access the K.ACCOUNT_NO but you can INNER JOIN with TABLE1.

WHERE ACCOUNT_NO = K.ACCOUNT_NO

is replaced

INNER JOIN TABLE1 ON TABLE2.ACCOUNT_NO = TABLE1.ACCOUNT_NO AND TABLE1.STATUS = 'A'

I hope it is helpful.

SELECT SUBSIDIARY, MAIN_ACCOUNT, AMOUNT ... FROM (
    ...
    LEFT JOIN (SELECT MAIN_ACCOUNT, ACCOUNT_NO AS SUBSIDIARY FROM TABLE1 WHERE STATUS = 'A') K
    ON MAIN_ACCOUNT = K.MAIN_ACCOUNT
    --this is where i got into a problem
    LEFT JOIN (SELECT SUM(AMOUNT) AS AMOUNT, ACCOUNT_NO, FROM TABLE2 INNER JOIN TABLE1 ON TABLE2.ACCOUNT_NO = TABLE1.ACCOUNT_NO AND TABLE1.STATUS = 'A' GROUP BY ACCOUNT_NO) L
    ON SUBSIDIARY = L.ACCOUNT_NO
    ...
    );

Collected from the Internet

Please contact [email protected] to delete if infringement.