OraFAQ Forum: Data Integration » How to Avoid Updating of Non-Null Column Value w/an Incoming NULL Column Value from Raw File

Home » Developer & Programmer » Data Integration » How to Avoid Updating of Non-Null Column Value w/an Incoming NULL Column Value from Raw File (ODI)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to Avoid Updating of Non-Null Column Value w/an Incoming NULL Column Value from Raw File [message #685585]

Tue, 15 February 2022 14:53

bmccollum
Messages: 15
Registered: April 2020

Junior Member

I am very much a novice in using Oracle Data Integrator. I've been assigned a small project with my employer to make the necessary modification(s) to an existing ODI flow and I'm stumped on how/where to do this.

I'm very familiar with the SQL Server ETL tool (SQL Server Integration Services), but again, unfortunately not familiar with the Oracle ODI suite.

Basically, we have a raw file that's provided to our organization several times per year. This raw file contains approx. 6 email-related columns. In more recent raw files that we've been provided, it's become apparent that the provider of these raw files has removed the email-related data from a lot of the 5 or 6 different email-related fields present in the raw file.

We currently have a large number of email addresses present in our Oracle database table from previously ODI-processed raw files from when these email addresses were still being provided in the raw file.

What I'd like to do, going forward with ongoing raw files provided to us, in the mapping between the raw file and the table... if records in the raw file are found to already exist in the Oracle table and therefore just an update of an existing record in the Oracle table is needed, I don't want to override existing non-null values in the 5-6 email-related Oracle table columns with what is increasingly a NULL email value in the 5-6 email-related fields present in the raw file. I want to preserve the email-related data that we have as opposed to updating records with null email values.

I have no idea how/where to implement this logic. Knowledge module? Some sort of adjustment that can be made to implement this logic in the ODI mapping between the raw file and the Oracle table? I'm at a loss and again, have never worked with ODI until now.

Much appreciation if anyone can point me in the right direction for this seemingly simplistic task.

Report message to a moderator

Re: How to Avoid Updating of Non-Null Column Value w/an Incoming NULL Column Value from Raw File [message #685589 is a reply to message #685585]

Tue, 15 February 2022 15:15

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Wed, 10 June 2020 07:29

Same answer than in your first topics:

...

And we are still waiting for your feedback in your other ones.

Report message to a moderator

Re: How to Avoid Updating of Non-Null Column Value w/an Incoming NULL Column Value from Raw File [message #685590 is a reply to message #685589]

Tue, 15 February 2022 15:22

bmccollum
Messages: 15
Registered: April 2020

Junior Member

I don't at all follow what your response means.....

Report message to a moderator

Re: How to Avoid Updating of Non-Null Column Value w/an Incoming NULL Column Value from Raw File [message #685591 is a reply to message #685590]

Tue, 15 February 2022 15:29

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You posted requests (created topics), you got answers, you didn't feedback to them (even when you said you will).
Is this clear?

Report message to a moderator

Re: How to Avoid Updating of Non-Null Column Value w/an Incoming NULL Column Value from Raw File [message #685592 is a reply to message #685591]

Tue, 15 February 2022 17:21

bmccollum
Messages: 15
Registered: April 2020

Junior Member

I have figured out a solution to this issue and this post may be closed. Thanks.

Report message to a moderator

Re: How to Avoid Updating of Non-Null Column Value w/an Incoming NULL Column Value from Raw File [message #685593 is a reply to message #685592]

Wed, 16 February 2022 00:14

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Then post the solution you figured.

Report message to a moderator

Re: How to Avoid Updating of Non-Null Column Value w/an Incoming NULL Column Value from Raw File [message #685602 is a reply to message #685593]

Wed, 16 February 2022 10:49

bmccollum
Messages: 15
Registered: April 2020

Junior Member

Solution revolved around the fact that I'm still very unfamiliar in navigating around thru the ODI Suite interface. Finally found the options to click on in the ODI Suite to get to the area where a existing "Update" statement was in order to view & edit that "Update" statement.

--- Went to ODI Studio
--- Clicked on "Designer" tab on left
--- Clicked on the name of my desired project
--- Clicked "Knowledge Modules" > "Integration"
--- Clicked on the unique name of the custom knowledge module I needed to access
--- Clicked "Used By" and got the name of the session that uses this custom knowledge module
--- Went to "Operator" tab on upper left
--- Click "Sessions List" header
--- Went to "Sessions" > <name of my desired session found a few steps earlier>
--- Clicked "Expand"
--- Right-clicked first green-checked item > Open
--- Clicked "Steps" tab on the right of screen
--- Under "Steps Hierarcy" section, I found and clicked a step named "Update existing rows..."
--- Right-clicked "Update existing rows..." item, clicked "Open session task"
--- Clicked "Code" tab
--- Clicked "Query/Execution Plan" link & then arrived at the existing "Update" statement that I was now able to edit as desired.

Report message to a moderator

Re: How to Avoid Updating of Non-Null Column Value w/an Incoming NULL Column Value from Raw File [message #685604 is a reply to message #685602]

Wed, 16 February 2022 11:12

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks, this will help future readers with same issue.

Report message to a moderator

Previous Topic:	Upgrade 11G Repository to 12c Repository
Next Topic:	Knowledge Module - Can You Use Hard-Coded SQL Instead of Dynamic SQL?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 22 19:29:24 CDT 2025