'How do I resolve my error message I got with Merge Query below?

I am writing a merge query but I am getting error when running it. I reviewed the query closely and I can not see why I am getting this error. I reviewed the different formats a SQL merge can take and still none of them worked. I am running this query on a SQL Develoer Version 19.1.0.094.

I realize I could use an 'Inner Join" for this solution but I was unsure of how to capture the 'When Matched' and 'When Not Matched' function. Is there an equivalent to the Match function when not using the Merge function? I appreciate any assistance with this problem.

I would appreciate any assistance on this manner.

MERGE into LAB_SALES_Fact fact
USING (select * from lab_stage_good stage)

On
(fact.time_id = stage.time_id AND
fact.cust_id = stage.cust_id AND
fact.promo_id = stage.promo_id AND
fact.channel_id = stage.channel_id)

WHEN MATCHED THEN UPDATE SET
fact.amount_sold = stage.amount_sold

WHEN NOT MATCHED THEN INSERT VALUES
(stage.prod_id, stage.cust_id, stage.time_id, stage.channel_id, stage.promo_id, 
stage.amount_sold)

Error at Command Line : 6 Column : 22
Error report -
SQL Error: ORA-00904: "STAGE"."CHANNEL_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:

sqloraclemerge


Solution 1:[1]

The table alias stage is not visible outside the subquery. Move the alias so that it is visible:

MERGE into LAB_SALES_Fact fact
USING (select * from lab_stage_good) stage
ON (fact.time_id = stage.time_id
AND fact.cust_id = stage.cust_id
AND fact.promo_id = stage.promo_id
AND fact.channel_id = stage.channel_id )
WHEN MATCHED THEN
  UPDATE
  SET fact.amount_sold = stage.amount_sold
WHEN NOT MATCHED THEN
  INSERT (
    prod_id,
    cust_id,
    time_id,
    channel_id,
    promo_id,
    amount_sold
  ) VALUES (
    stage.prod_id,
    stage.cust_id,
    stage.time_id,
    stage.channel_id,
    stage.promo_id, 
    stage.amount_sold
  )

Note: also name the columns you are inserting into.

Or, even simpler, do not use a sub-query:

MERGE into LAB_SALES_Fact fact
USING lab_stage_good stage
ON ...

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions