|ODI - Integration Knowledge Modules ("Target Command" SQL Question) [message #685639]
||Tue, 22 February 2022 13:14
Registered: April 2020
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:
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.
declare cursor myCursor is
where IND_UPDATE = 'U'
/* Loop over the Cursor and execute the update statement */
for aRecord in myCursor loop
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
Thanks for any clarification anyone can provide.