oracle sql issue with merge query

Question

can you help me resolving the error for the merge query. query :

MERGE INTO TEST_STATUS_REV TB1
USING
(
SELECT
TB2.ITG,'1',TB2.CORP_LOAD_ID,TB2.LVL_CYC,'',TB2.BUS_FUNC,'',COUNT(TB2.status) AS Passed,'','','','',TB2.IMPT_ENG,TB2.VAL_TYP,'',''
FROM test_status_rev_interface@TAA1_PROD TB2
where TB2.status like 'Pass%'
and TB2.lvl_cyc='L3C1'
GROUP BY
        TB2.ITG,
--      TB2.RTS,            
        TB2.CORP_LOAD_ID,
        TB2.LVL_CYC,
--      CONF,
        TB2.BUS_FUNC ,
--      TB2.TC_PLN,
--      TB2.TC_PASSED,
--      TB2.TC_FAILED,
--      TB2.NAV,
--      TB2.PND
--      TB2.COM,
        TB2.IMPT_ENG,
        TB2.VAL_TYP
--      TB2.TC_TOT
--      TB2.FEAT_ID
   ) SRC
ON 
(
TB1.LVL_CYC = SRC.LVL_CYC
)
WHEN MATCHED THEN UPDATE SET TB1.TC_PASSED = SRC.STATUS;

error :

Error report:
SQL Error: ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"

i have tried fixing it many possible ways but still the issue persists.

try to take into comment the '1' after TB2.ITG, and try to clear your query, the lot of comment it's quite confusing — Thomas
– Thomas, Commented Sep 15, 2015 at 7:53

przemo_pl · Accepted Answer · 2015-09-15 08:13:31Z

1

I have no way of checking it on the pl/sql dev right now but I would blame the source query statement. You need to name all the '' or '1' columns in order to call them later in the 'WHEN' clause! Please try and tell us if that worked :) EDIT: It should be:

TB2.ITG,'1' as a,TB2.CORP_LOAD_ID,TB2.LVL_CYC,'' as b,TB2.BUS_FUNC,'' as c,COUNT(TB2.status) AS Passed,'' as d,'' as e,'' as f,'' as g,TB2.IMPT_ENG,TB2.VAL_TYP,'' as h,'' as i

answered Sep 15, 2015 at 8:13

przemo_pl

871 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

akira Over a year ago

The select query is working fine if it is run alone.. when it is used along with merge query i am getting the mentioned error.

David Aldridge Over a year ago

That doesn't mean that this is not the solution though. When you run the query on its own, it doesn't matter what the columns are called -- in an inline view it definitely does.

przemo_pl Over a year ago

if you are going to reference the column in any way - you should use an alias. This is why the query worked standalone. if you put it in a select * from (your query) it would complain as well!. For me best practice maybe is just to use aliases always - you won't need to remember when to use them and when you can not.

David Aldridge · Accepted Answer · 2015-09-15 08:29:31Z

0

Well you do not need the table alias in the SET clause. That should read:

WHEN MATCHED THEN UPDATE SET TC_PASSED = SRC.STATUS;

Not sure that's the fix though.

answered Sep 15, 2015 at 8:29

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Collectives™ on Stack Overflow

oracle sql issue with merge query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related