CODEWITHSUNDEEP
sqloracle-databaseoracle10gora-00904
Remoh
Remoh

Reputation: 134

Why won't this merge statement work?

I've spent the better part of the day trying to determine why a merge statement won't work and I'm starting to think the problem must be something a bit exotic.

My database has dozens of PL/SQL procedures that use merge statements but I absolutely cannot get one in particular to work. Although it's much larger than the example shown, I've stripped it down so that it only updates a couple of columns and it still will not compile.

The error is 'ORA-00904 "alias"."column_name" invalid identifier'. This typically means that a column name was mistyped or, in the case of a merge, you are attempting to update a field that's used in a join. This is definately NOT the case. I've quadrupled-checked and the column names are right, they all exist and the format of the statement is exactly the same as what I'm using in many other place.

    /** 
    Result: ORA-00904 "P"."SFDC_CUST_CONTACT_PK": invalid identifier

    I'm certain that the table and column names are all correct.

    If I join on any of the dozen or so other columns instead, I 
    get the exact same error.

    Note: I'm NOT attempting to update the column that I join
    against.


    **/

    merge into customer_contact c
    using (select p.fax_number,
           p.email
    from sfdc_cust_contact_temp p
    ) p
    on (p.sfdc_cust_contact_pk = c.sfdc_cust_contact_pk)
    when matched then
      update set 
      c.fax_number = p.fax_number,
      c.email = p.email;


    /*** 

    This works fine on the same machine 

    **/ 
    merge into customer_contact_legacy c
    using (select ct.contact_legacy_pk, 
          ct.fax_number,
          ct.email 
    from customer_contact_temp ct 
    ) ct
    on (upper(trim(ct.contact_legacy_pk)) = upper(trim(c.contact_legacy_pk)))
    when matched then
      update set 
      c.fax_number = ct.fax_number,
      c.email = ct.email;

Any ideas what else could be wrong here? Could there be some type of corruption with the table?

The version is 10g.

Upvotes: 1

Views: 410

Answers (1)

John Fisher
John Fisher

Reputation: 22719

It looks like your using clause is missing the column you're trying to join on.

Your code:

merge into customer_contact c
using (select p.fax_number,
       p.email
from sfdc_cust_contact_temp p
) p
on (p.sfdc_cust_contact_pk = c.sfdc_cust_contact_pk)

Potential fix:

merge into customer_contact c
using (select p.sfdc_cust_contact_pk,
       p.fax_number,
       p.email
from sfdc_cust_contact_temp p
) p
on (p.sfdc_cust_contact_pk = c.sfdc_cust_contact_pk)

Upvotes: 4

Related Questions