Hi.
I have a process that does looping through a table, checking on values from other tables based on some cursor values, doing count of records, checking the counts and conditionally inserting or updating data in both the table being looped through itself and some other tables.
I have implemented the process in PL/SQL within a few hours. No problem.
We are primarily using ODI for ETL and I wonder if and how this would be implemented in a mapping.
I have hugely simplified our process to just a few manipulations to make my point. Here it goes...
for cur in (select rowid, A_col1, A_col2, A_col3 from A) -- this I can do with a starter datastore
loop
select count(*) -- this I can do with an Filter and Aggregate components
into v_B_col1
from B
where B_col2 = cur.A_col2;
if v_B_col1 > 0 then -- this I can do with a Split component
update A -- how can I do an actual update of a single row?
set A_col3 = 'needs updating'
where A_col1 = rowid;
end if;
end loop;
--
I think this can be accomplished in mapping and I have a pretty good idea what components to use and in what order. Except for the last one - the single-row update.
What component on a mapping can use for that? Incremental update IKM? Don't I need an update key for that? ROWID is not recognized as an update key.
Another method?
Thank you
Boris