CODEWITHSUNDEEP
sqloracle-databasemergeora-00904
param
param

Reputation: 25

MERGE statement ERRORS

I get this error when running my MERGE statement: 

Error report -
SQL Error: ORA-00904: "SRC"."TB2"."WAERS": invalid identifier
00904. 00000 -  "%s: invalid identifier".

This is my statement:

MERGE INTO costing. TB1 USING
(SELECT costing.TB1.ROWID row_id,
        costing.TB2.WAERS
   FROM costing.TB2
   JOIN costing.TB3
     ON costing.TB2.BUKRS = costing.TB3.BUKRS
   JOIN costing.TB1
     ON costing.TB3.MANDT = Client
    AND costing.TB3.BWKEY = costing.TB1.Plant
  WHERE Currency_Conversion_Status <> 'Pricing Missing'
) src ON ( costing.TB1.ROWID = src.row_id )
WHEN MATCHED THEN
  UPDATE SET Currency = src.TB2.WAERS;

Upvotes: 1

Views: 165

Answers (1)

Boneist
Boneist

Reputation: 23578

You're attempting to use aliases that you defined in the src subquery outside the subquery. That's never going to work.

I would suggest using table aliases to make column references easier to see - here is what I would do:

merge into costing.tb1 tgt
using (select t1.rowid row_id,
              t2.waers
       from   costing.tb2 t2
              join costing.tb3 t3 on t2.bukrs = t3.bukrs
              join costing.tb1 t1 on t3.mandt = client -- missing alias on client
                                  and t3.bwkey = t1.plant
       where  currency_conversion_status <> 'Pricing Missing' -- missing alias on currency_conversion_status
      ) src 
  on (tgt.rowid = src.row_id )
when matched then
  update set tgt.currency = src.waers;

I would advise you to also add in the missing aliases from client and currency_conversion_status, so that you can tell which tables they belong to.

If client is a column in costing.tb1, then it's possible you don't need to include the join to tb1 in the subquery, you can just join it directly in the MERGE statement, something like:

merge into costing.tb1 tgt
using (select t3.mandt,
              t3.bwkey,
              t2.waers
       from   costing.tb2 t2
              join costing.tb3 t3 on t2.bukrs = t3.bukrs
       where  currency_conversion_status <> 'Pricing Missing' -- missing alias on currency_conversion_status
      ) src 
  on (tgt.client = src.mandt
      and tgt.plant = src.bwkey)
when matched then
  update set tgt.currency = src.waers;

although since I don't know which table currency_conversion_status belongs to - you may have to move where the where clause goes.

By doing this, you're removing an unnecessary join to the costing.tb1 table, which should improve the performance of the statement.

Upvotes: 1

Related Questions