Oracle 11g merge Invalid Identifier with subquery in the INSERT VALUES

Question

I am trying to use MERGE in a procedure and in the insert values I would like to use a query using the USING_SET

The MERGE looks like below:

MERGE INTO N_CUSTOMER dest
   USING S_customer src 
    ON (dest.CUSTOMER_ID = src.CUSTOMER_ID)
    WHEN
     MATCHED THEN
     update set
     dest.DATE_OF_BIRTH = src.S_BIRTHDATE,
     dest.GENDER = src.S_GENDER,
     dest.NAME = src.S_NAME
     WHEN NOT MATCHED THEN
     INSERT (DATE_OF_BIRTH, GENDER, NAME, TOTAL_FOO_COUNT)
     VALUES (src.S_BIRTHDATE, src.S_GENDER, src.S_NAME, 
            (select count(f.FOO_ID) from S_FOO f where f.CUSTOMER_ID = src.CUSTOMER_ID));

The error that I got is:

invalid SQL Error: ORA-00904: "SRC"."CUSTOMER_ID": invalid identifier in the subquery (select count(f.FOO_ID) from S_FOO f where f.CUSTOMER_ID = src.CUSTOMER_ID).

Some how oracle cannot recognize the "src" inside the Subquery. Any ideas?

void · Accepted Answer · 2015-03-05 10:11:05Z

1

you can use a outer variable:

declare 
o_variable number;

begin

select count(f.FOO_ID) 
into o_variable 
from S_FOO f where f.CUSTOMER_ID in (select CUSTOMER_ID from S_customer.CUSTOMER_ID);

MERGE INTO N_CUSTOMER dest
   USING S_customer src 
    ON (dest.CUSTOMER_ID = src.CUSTOMER_ID)
    WHEN
     MATCHED THEN
     update set
     dest.DATE_OF_BIRTH = src.S_BIRTHDATE,
     dest.GENDER = src.S_GENDER,
     dest.NAME = src.S_NAME
     WHEN NOT MATCHED THEN
     INSERT (DATE_OF_BIRTH, GENDER, NAME, TOTAL_FOO_COUNT)
     VALUES (src.S_BIRTHDATE, src.S_GENDER, src.S_NAME,o_variable);
end;

answered Mar 5, 2015 at 10:11

void

7,9403 gold badges29 silver badges47 bronze badges

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

Comments

Exhausted · Accepted Answer · 2015-03-05 10:05:28Z

0

you could use subquery in src itself something like below

MERGE INTO N_CUSTOMER dest
   USING 
    (
    select cus.*,k.cnt
    S_customer cus join
    (select count(f.FOO_ID) cnt ,CUSTOMER_ID 
        from S_FOO f group by CUSTOMER_ID ) k
    on cus.CUSTOMER_ID = k.CUSTOMER_ID 
    ) src 
    ON (dest.CUSTOMER_ID = src.CUSTOMER_ID)
    WHEN
     MATCHED THEN
     update set
     dest.DATE_OF_BIRTH = src.S_BIRTHDATE,
     dest.GENDER = src.S_GENDER,
     dest.NAME = src.S_NAME
     WHEN NOT MATCHED THEN
     INSERT (DATE_OF_BIRTH, GENDER, NAME, TOTAL_FOO_COUNT)
     VALUES (src.S_BIRTHDATE, src.S_GENDER, src.S_NAME, src.cnt);

answered Mar 5, 2015 at 10:05

Exhausted

1,8932 gold badges23 silver badges33 bronze badges

2 Comments

marma_nik Over a year ago

Yes you are right but I am trying to create this merge with the lowest cost. So I am trying to avoid the subquery in using_set.

Exhausted Over a year ago

post some CREATE scripts and INSERT statements will be useful.

Collectives™ on Stack Overflow

Oracle 11g merge Invalid Identifier with subquery in the INSERT VALUES

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related