1

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?

  • 1
    (Probably) unrelated, but it's best practice to specify the attributes you want to select, instead of * – HoneyBadger Aug 5 '16 at 13:36
4

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.

  • There are around 500 columns. Do I really have to list them all? That's not very practical. – Nickpick Aug 5 '16 at 15:13
  • 1
    If you only want to return a single table's information, you can try alias.* to select all the columns from a that table only. (e.g. TLEA.*) If you need certain columns from certain tables, or all columns from both tables, you'll have to list them. – Haris Khan Aug 5 '16 at 15:16
  • In MySQL, there is a information_schema database where we can find the name of all columns for specific table. I do not know about Oracle. I hope there will be something like that which might be helpful for you. – Chandan Aug 5 '16 at 18:03
  • 1
    This query will give you the column names for the select SELECT listagg(column_name,',') WITHIN GROUP (ORDER BY Column_Name) Columns FROM user_tab_cols WHERE table_name='TLEA' AND owner='DF_RISK_CUAT_OWNER'; Depending on the number of columns and lengths, you may have to add more filters and run it multiple times. – Leigh Riffel Aug 5 '16 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.