Joining two tables with an alias

aliasjoin;oracle

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

Update:
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?

Best Answer

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.

Related Question