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?
It’s because you’re using the SELECT * and joining a table to itself. The DB engine will try to return all columns, so it needs to know which columns you are referring to if you have multiple with the same name. If you want both you need to specify them with aliases to indicate which order to output them with.
The reason the aggregation works is because you’re not trying to return any duplicated columns. Replace the SELECT * with the columns that you want, using the Aliases to specify which tables you want them from.
As a general best practice, do not use SELECT *; always specify your column names.