My Oracle Support Banner

Siebel List/Job Import Using Exact Match on Explicit or Implicitly Joined Column Fails To Match and Error "ORA-00904 invalid identifier" Is Seen In The Log (Doc ID 1995328.1)

Last updated on MARCH 19, 2024

Applies to:

Siebel Marketing - Version 8.1.1 [21112] to 8.1.1.14.13 [IP2014] [Release V8]
Siebel Marketing - Version 17.0 [IP2017] to 24.2 [Release V17]
Information in this document applies to any platform.

Symptoms

When using an Extension Table Column configured as a Joined field in the Business Component and then used as an Exact Match Column in the Import Mapping, no Match is found.

The Import Job completes, however, in the Workflow Process Manager Log the error below is found:

SELECT S_CONTACT.ROW_ID ,S_CONTACT_IMPT.ROW_ID AS STAGERID FROM SIEBEL.S_CONTACT S_CONTACT ,SIEBEL.S_CONTACT_IMPT S_CONTACT_IMPT WHERE UPPER(S_CONTACT_X.X_MSISDN)=UPPER(S_CONTACT_IMPT.X_MSISDN) AND CONTACT_ID IS NULL AND IMPRT_TSK_ID = '1-QWZJT' AND IMPRTRUN_SEQ_NUM ='1-QXJK3' AND IMPTPROC_BATCH_NUM = '2'
DBCLog DBCLogError 1 0000002554d80c2c:0 2015-02-09 15:11:05 [DataDirect][ODBC Oracle driver][Oracle]ORA-00904: "S_CONTACT_X"."X_MSISDN": invalid identifier
MktgGenericEvent Info 4 0000002554d80c2c:0 2015-02-09 15:11:05 End of Perform Dedupe. 

Custom fields mapped to extension columns on base table S_CONTACT work when Exact Match is set on these columns.

It is expected that Exact Match works for custom fields mapped to extension tables such as S_CONTACT_X as it does for fields mapped to extension columns on S_CONTACT base table.

Using "Contact" object as an example, the issue can be reproduced with the following steps:

1. In Tools or Web Tools, create a new column on S_CONTACT_X table called 'X_MSISDN'

2. Create a new field 'VFH MSISDN' in Contact BC mapped to S_CONTACT_X.X_MSISDN

3. Create a new column on S_CONTACT_IMPT table called 'X_MSISDN'

4. On the 'Contact Stager' BC create a new field called 'VFH MSISDN'

5. Add the User Property below to the 'Contact Stager' BC with following values:

Name: Map:Contact.VFH MSISDN
Value: Contact Stager.VFH MSISDN:S_CONTACT_IMPT_X.X_MSISDN

6. On the 'Contact Integration Object_SIA' IO > 'Contact' IC add the 'VFH MSISDN' IC Field

7. On the 'Contact Disp Integration Object_SIA' IO > 'Contact' IC add the 'VFH MSISDN' IC Field

8. Compile/ Deliver the changes

9. In the Application, go to Administration - Marketing > Import Mappings and create a new import mapping using the 'VFH MSISDN' field with Exact Match = Y, e.g.

Import Mapping
Object Name Field Name Exact Match Column
Contact Last Name  
Contact First Name  
Contact Email Address 2  
Contact VFH MSISDN Y
Contact Integration Id  
Contact Comment  
Contact Middle Name  


10. Create a new Import Job via Administration - Marketing > Import, e.g.

Source Type:                       Text File
Main Object:                       Contact
Import Mapping:                 contact_new_email_update
Import Mode:                     Import new records
Match Resolution Guideline: Overwrite Existing Record

File Selection:                    Use exact file name
File Name:                        contact_new_email_update
Data Type:                        Comma Separated
Delimiter:                         ,
Header Row Included:       Y


11. Activate the job.

12. Notice that the Contact was inserted rather than updating the existing Contact.

Changes

Custom Field added to the Job Import as Exact Match

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.