I have the following query which casues some problems: it complains that
ORA-00904: "TLEA"."TLSN_TRD_ID": invalid identifier even though TLSN_TRD_ID is clearly in the TLEA table. It is as if the aliasing doesn't work correctly. Both the RECON and TLEA table work separately but when I try to do a left join as below I get the error. Any suggestions what could be the problem?
SELECT * FROM ( SELECT * FROM DF_RISK_SIT2_owner.RECON_RESULTS WHERE (RS_ONLY = 1 or VERSION_MISMATCH = 1) AND REC_SYSTEM = 'SUMMIT' )RECON LEFT JOIN ( SELECT * FROM DF_RISK_CUAT_owner.TLEA INNER JOIN (SELECT TLSN_TRD_ID,TLSN_LEG_ID, MAX(TLEA_COB_DT) AS MDate FROM DF_RISK_CUAT_owner.TLEA GROUP BY TLSN_TRD_ID,TLSN_LEG_ID )MaxDate ON MaxDate.TLSN_TRD_ID = DF_RISK_CUAT_owner.TLEA.TLSN_TRD_ID AND MaxDate.MDate = DF_RISK_CUAT_owner.TLEA.TLEA_COB_DT AND MaxDate.TLSN_TRD_ID='C8112832LX' )TLEA ON RECON.RS_TRD_ID = TLEA.TLSN_TRD_ID
It appears that the problem is related to duplicate column names:
When i replace the line below the
LEFT JOIN with
SELECT MaxDate.TLSN_TRD_ID AS "A_A_TLSN_TRD_ID" and then join on
TLSA.A_A_TLSN_TRD_ID it works. Strangely it doesn't work when I do on the join
TLEA.MaxDate.TLSN_TRD_ID. Any idea why?