Home » Developer & Programmer » Data Integration » Knowledge Module - Can You Use Hard-Coded SQL Instead of Dynamic SQL? (ODI)
Knowledge Module - Can You Use Hard-Coded SQL Instead of Dynamic SQL? [message #685616] Thu, 17 February 2022 14:01
bmccollum
Messages: 14
Registered: April 2020
Junior Member
I thought I'd found the correct place to make a modification to an "Update..." SQL statement for an existing ODI project that was worked on a while back by a contract developer, but I was wrong.

For this ODI project, I've gone to "Knowledge Modules" within the project's menu, right-clicked the specific knowledge module that contains the "Update..." task that I wish to edit, and clicked "Open".

I've clicked the "Tasks" tab, selected the task item (named "Update existing rows") that I wish to make modifications to, & clicked on the "Target Command" column's contents that I wish to modify.

I'm presented with the following dynamic SQL statement:
/* Use of a PL-SQL bloc to perform the update (management of LONGS and LOBS etc.) */
<%{
   String targetColsMappedFromSource = odiRef.getColList("", "T.[COL_NAME]", ",\n\t", "", "((UPD AND (NOT UK) AND (NOT TRG)) AND REW)");
   String targetColsMappedOnTarget = odiRef.getColList(odiRef.getColList(",", " ", "", "", "((UPD AND (NOT UK) AND (NOT TRG)) AND REW)"), "T.[COL_NAME]", ",\n\t", "", "((UPD AND (NOT UK) AND TRG) AND REW)");
   if (targetColsMappedFromSource.trim().length() == 0 &&
       targetColsMappedOnTarget.trim().length() == 0)
       throw new OdiKMException( "More target column mappings needed: at least one target column, which is not part of the update key, must have a mapping expression assigned." );
}%>declare cursor myCursor is
select      	<%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "((UPD AND (NOT TRG)) AND REW)")%>
	<%=odiRef.getColList(",", "[EXPRESSION] [COL_NAME]", ",\n\t", "", "((UPD AND TRG) AND REW)")%>
from    	<%=odiRef.getTable("L", "INT_NAME", "W")%>
where    	IND_UPDATE = 'U'
;
begin
	/* Loop over the Cursor and execute the update statement */
	for aRecord in myCursor loop
		update	<%=odiRef.getTable("L", "TARG_NAME", "A")%>
		set	<%=odiRef.getColList("", "[COL_NAME]\t= aRecord.[COL_NAME]", ",\n\t\t\t", "", "((UPD AND (NOT UK) AND (NOT TRG)) AND REW)")%>
			<%=odiRef.getColList(odiRef.getColList(",", " ", "", "", "((UPD AND (NOT UK) AND (NOT TRG)) AND REW)"), "[COL_NAME]\t= aRecord.[COL_NAME]", ",\n\t\t\t", "", "((UPD AND (NOT UK) AND TRG) AND REW)")%>
		where    	<%=odiRef.getColList("", "\t[COL_NAME]\t= aRecord.[COL_NAME]", "\n\t\tand\t", "", "(UK)")%>
		;
	end loop;
end;

This code ultimately translates to the following once it's built & executed (Note that I've removed a lot of it as the resulting "Update" statement is very lengthy, so you'll get the idea based on the abbreviated code shown below as to the "final" SQL statement that's built using this dynamic SQL):
/* Use of a PL-SQL bloc to perform the update (management of LONGS and LOBS etc.) */
declare cursor myCursor is
select      	GOVTBUSEMAIL,
	ALTGOVTBUSEMAIL,
	PASTPERFBUSEMAIL,
	ALTPASTPERFBUSEMAIL,
	ELECBUSEMAIL,
	ALTELECBUSEMAIL
from    	GRT.I$_TBSAMDATA
where    	IND_UPDATE = 'U'
;
begin
	/* Loop over the Cursor and execute the update statement */
	for aRecord in myCursor loop
		update	GRT.TBSAMDATA
		set	GOVTBUSEMAIL	= aRecord.GOVTBUSEMAIL,
			ALTGOVTBUSEMAIL	= aRecord.ALTGOVTBUSEMAIL,
			PASTPERFBUSEMAIL	= aRecord.PASTPERFBUSEMAIL,
			ALTPASTPERFBUSEMAIL	= aRecord.ALTPASTPERFBUSEMAIL,
			ELECBUSEMAIL	= aRecord.ELECBUSEMAIL,
			ALTELECBUSEMAIL	= aRecord.ALTELECBUSEMAIL
		where    		CAGE	= aRecord.CAGE
		;
	end loop;
end;
What I'd like this "Update..." statement in this specific knowledge module task to do is to update these specific email-related columns ONLY if the equivalent columns from the raw file I'm reading contain a non-null value. In other words, if a record in my GRT.tbSamData Oracle table's columns has data in there at present, I don't want to override an existing email address in a column with a null value for the record coming in from the raw data file. So, I'd essentially like to have the following as the code.:
/* Use of a PL-SQL bloc to perform the update (management of LONGS and LOBS etc.) */
declare cursor myCursor is
select      	GOVTBUSEMAIL,
	ALTGOVTBUSEMAIL,
	PASTPERFBUSEMAIL,
	ALTPASTPERFBUSEMAIL,
	ELECBUSEMAIL,
	ALTELECBUSEMAIL
from    	GRT.I$_TBSAMDATA
where    	IND_UPDATE = 'U'
;
begin
	/* Loop over the Cursor and execute the update statement */
	for aRecord in myCursor loop
		update	GRT.TBSAMDATA
		set	GOVTBUSEMAIL	= NVL(aRecord.GOVTBUSEMAIL, GOVTBUSEMAIL),
			ALTGOVTBUSEMAIL	= NVL(aRecord.ALTGOVTBUSEMAIL, ALTGOVTBUSEMAIL),
			PASTPERFBUSEMAIL	= NVL(aRecord.PASTPERFBUSEMAIL, PASTPERFBUSEMAIL),
			ALTPASTPERFBUSEMAIL	= NVL(aRecord.ALTPASTPERFBUSEMAIL, ALTPASTPERFBUSEMAIL),
			ELECBUSEMAIL	= NVL(aRecord.ELECBUSEMAIL, ELECBUSEMAIL),
			ALTELECBUSEMAIL	= NVL(aRecord.ALTELECBUSEMAIL, ALTELECBUSEMAIL)
		where    		CAGE	= aRecord.CAGE
		;
	end loop;
end;
Instead of the very first code block that I posted that has what I guess I would call dynamic SQL that dynamically assembled the entire "Update..." statement, can I just simply replace this dynamic SQL for that task item with the hard-coded SQL that I posted in the 3rd example above?

I'd be fine staying with the dynamic SQL, but I don't know how to keep the dynamic SQL in place and only have it apply the NVL function to these 6 specific columns shown above. For the remaining columns that are part of this "Update..." statement (there are roughly 80-90 columns that are involved in being updated), I don't want to have them updated using the NVL function and they can just be left to update as-is and it's fine if those columns are updated with null values. I just don't want the 6 email-related columns to be updated with any null values coming in from the raw file.

Thank you in advance for any direction/suggestions you're able to provide.
Previous Topic: How to Avoid Updating of Non-Null Column Value w/an Incoming NULL Column Value from Raw File
Next Topic: ODI - Integration Knowledge Modules ("Target Command" SQL Question)
Goto Forum:
  


Current Time: Mon Jan 30 22:02:25 CST 2023