OraFAQ Forum: Data Integration » ODI - Integration Knowledge Modules ("Target Command" SQL Question)

Home » Developer & Programmer » Data Integration » ODI - Integration Knowledge Modules ("Target Command" SQL Question) (ODI)

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

ODI - Integration Knowledge Modules ("Target Command" SQL Question) [message #685639]

Tue, 22 February 2022 13:14

bmccollum
Messages: 15
Registered: April 2020

Junior Member

I posted a longer version related to this issue. The question has received over 2500 views but no responses as of yet.

I thought I'd make an attempt at posting a shorter, more succinct version of the question.

In ODI, I have an Incremental Update Knowledge Module that I'm wanting to modify. The current knowledge module conducts the desired "Update" statement via what I guess you'd call dynamic SQL.

What I'm wanting to do, for just 6 columns involved in the Update statement, is to not overwrite the values in any of the 6 columns in the table that will be updated if the corresponding columns in the raw/source file are null. For the remaining 70-or-so columns, I'm perfectly fine with whatever is in a matching column in the raw/source file to be updated (even if it's a null value that will update an existing column's value that's non-null in the target table). I'd be using the NVL function for the 6 specific columns.

I don't see a way via the dynamic SQL that's currently in place as one of the tasks for this Knowledge Module to indicate specifically for just 6 of the columns that I want the NVL function to be applied, as it results in a "regular" update being made column-to-column for all of the columns.

Can a regular update statement be implemented in a Knowledge Module task to where I can code for this specific NVL usage for just 6 out of the 75-or-so columns that will be involved in the update statement? Such as:

declare cursor myCursor is
select      	Column1,
	Column2,
	Column3,
	EmailCol1,
	EmailCol2,
	EmailCol3,
	EmailCol4,
	EmailCol5,
	EmailCol6
from    	SourceFile
where    	IND_UPDATE = 'U'
;
begin
	/* Loop over the Cursor and execute the update statement */
	for aRecord in myCursor loop
		update	TargetTable
		set	Column1	= aRecord.Column1,
			Column2	= aRecord.Column2,
			Column3	= aRecord.Column3,
			EmailCol1	= NVL(aRecord.EmailCol1,EmailCol1),
			EmailCol2	= NVL(aRecord.EmailCol2,EmailCol2),
			EmailCol3	= NVL(aRecord.EmailCol3,EmailCol3),
			EmailCol4	= NVL(aRecord.EmailCol4,EmailCol4),
			EmailCol5	= NVL(aRecord.EmailCol5,EmailCol5),
			EmailCol6	= NVL(aRecord.EmailCol6,EmailCol6)
		where    		PrimaryKey	= aRecord.PrimaryKey
		;
	end loop;
end;

All I can find via extensive searches only shows the "dynamic SQL" as what's always in the "Target Command" area of the Knowlege Module tasks as opposed to hard-coded SQL like I've pasted above.

Thanks for any clarification anyone can provide.

Report message to a moderator