Column ambiguously defined but join properly aliased

Question

I have a column abigously defined error however. I have properly aliased all columns. All I would like to do is bring in avg_tkt_amt from a table named fdsna.t_mer_dim_na. It can join on the first table with primary_mer_num. I'm not sure why I keep getting this error.

SELECT
primary_ext_mid
, mer_dba_nam
, clg_num
, ent_num
, ent_nam
, mer_opn_dte
, CASE 
    WHEN mer_cls_dte > sysdate+30 
        THEN null 
    ELSE 
        mer_cls_dte 
    END mer_cls_dte
--, compliant_environment
--, certified_date
, MAX(lst_dep_dte) lst_dep_dte
, MIN(first_dep_dte) first_dep_dte
, CASE 
    WHEN period_date BETWEEN '1-jan-2018' AND '31-jan-2018' 
        THEN SUM(grss_vol) 
    ELSE 
        null 
    END jan_gross_vol
, CASE 
    WHEN period_date BETWEEN '1-feb-2018' AND '28-feb-2018' 
        THEN SUM(grss_vol) 
    ELSE 
        null 
    END feb_gross_vol
, CASE 
    WHEN period_date BETWEEN '1-mar-2018' AND '28-mar-2018' 
        THEN SUM(grss_vol) 
    ELSE 
        null 
    END mar_gross_vol
, SUM(grss_vol) AS gross_volume
--, avg_tkt_amt 
, SUM (DIAOE-equip_rev_amt) AS DIAO
, SUM(dia) AS DIA
,SUM(diaoe) AS DIAOE
,SUM(AMEX_ACQUIRED_TRN_DB_cnt - AMEX_ACQUIRED_TRN_CR_cnt + DEBIT_CARD_TRN_DB_cnt - DEBIT_CARD_TRN_CR_cnt + DISCOVER_ACQUIRED_TRN_DB_cnt - DISCOVER_ACQUIRED_TRN_CR_cnt + VISA_MC_ACQUIRED_TRN_DB_cnt - VISA_MC_ACQUIRED_TRN_CR_cnt) AS nettrncnt
,SUM(OTHER_INCOME_TRN_DB_cnt - OTHER_INCOME_TRN_CR_cnt) AS nettrncnt_other
,SUM(CASH_ADVANCE_TRN_DB_cnt - CASH_ADVANCE_TRN_CR_cnt) AS nettrncnt_cash
FROM bi.t_mer_dim_na a,
     stratus.T_SETT_NA b,
     bi.t_mer_bill_mth_na c,
     FDSNA.T_MER_DIM_NA d,
     (SELECT b.mid,a.compliant_environment,a.certified_date
      FROM eclipse.compliance_info a,eclipse.merch_mid b
      WHERE a.ecl_mid_tag=b.ecl_mid_tag 
     ) f
WHERE a.mid_tag = b.mid_tag
AND clg_num = 7
AND a.mer_dim_id = c.mer_dim_id
AND period_date >= to_date('01012018','mmddyyyy')
AND f.mid(+) = primary_ext_mid
--AND e.mid = primary_ext_mid 
AND d.external_mid = primary_mer_num
AND ent_num in ('45810','45811','46849','45948','45824','46911','45509','46845')
GROUP BY primary_ext_mid, mer_dba_nam,  clg_num, ent_num, mer_opn_dte, ent_nam, 
CASE 
   WHEN mer_cls_dte > sysdate+30 
        THEN null 
    ELSE 
        mer_cls_dte 
    END 
,period_date 
--) sq1

--left join FDSNA.T_MER_DIM_NA b
--on sq1.primary_ext_mid = b.primary_ext_mid
;

Answer 1

Once upon a time in a database far far away there were two tables who fancied each other. They spent their days doing what tables do - they just lay there.

create table table1(
   id1   varchar2(10)
  ,truth number
);

create table table2(
   id2 varchar2(10)  
);

One fine day a shiny SQL query came along and asked our tables if they wanted to join in the search of truth. They happily agreed, and they found the answer to life, universe and everything.

select id1     -- Only in table1
      ,id2     -- Only in table2
      ,truth   -- Only in table1
  from table1
  join table2 on(id1 = id2);

ID1          ID2               TRUTH
------------ ------------ ----------
A            A                    42

A few years went by and our tables were happy. Now, in every fairy tale there has to be a consultant villain and this one is no different. A vile monster had come to town and tried to convince table2 to search for its own truth using powerful black magic called denormalization.

alter table table2 add truth number;

Instantly, hell broke loose in the promised land because there is only room for one truth in this database.

select id1     -- Only in table1
      ,id2     -- Only in table2
      ,truth   -- OUCH! So many truths! who to trust? 
  from table1
  join table2 on(id1 = id2);

People were falling and dying in large numbers when the promise of one-single-truth no longer worked.

,truth
 *****
ERROR at line 3:
ORA-00918: column ambiguously defined

Table2 immediately regretted not talking to table1 about this and after an audience with the King, they managed to fix the problem by using fully qualified column names in the query. Later they passed a law to enforce table aliasing and fully qualified columns even for single-table queries!

select t1.id1
      ,t2.id2
      ,t1.truth   -- Specifically from table1
  from table1 t1
  join table2 t2 on(t1.id1 = t2.id2);

Moral of the story: Always use table aliasing and fully qualified column names. You will do this anyway eventually. It's just a matter of time.

Answer 2

You might be having column with same name in both the tables. That's why the error is displayed. Alias the table name with