1 Reply Latest reply on Nov 19, 2012 6:47 AM by 958953

    Need to update target data source in ODI

      Hi All,
      I have 2 tables 1. Tab_abc(col1,col2,col3,col4,col5) & 2. Tab_xyz(col1,col2,col3).

      Tab_abc has already some records with NULL values in col2& col3 and Tab_abc.col1 = Tab_xyz.col1
      I need to update col2 & col3 in Tab_abc.

      I have used IKM: oracle Incremental update
      and checked col2& col3 for update and made col1 as key.
      But it is giving some problem.

      COuld you please let me know, in deatail to solve this issue.

        • 1. Re: Need to update target data source in ODI
          It seems you are on the right track. On the target table , you have made col1 as key.

          The only issue is that it will update all your target columns ( col2 , col3 ) based on the entries of source columns ( col2 , col3 ).

          One of the prospective solution can be make a join of target with the source or simply put a filter to find out the rows which have col2 , col3 in target as null.
          on Source table put the filter
          Tab_abc.col1 in( Select Tab_xyz.col1 from Tab_xyz where Tab_xyz.col2 is null or Tab_xyz.col3 is null ).

          BUT in this case , col2 and col3 both values will get updated if any of the value for col2 ,col3 is null for particular row.

          If you want to update only null valued columns then you will need to create interface based on the following query :

          update Tab_xyz set (col2,col3) = ( select NVL(a.col2,B.col2),NVL(a.col3,B.col3) from Tab_abc B,Tab_xyz a
          where col3 is null or col2 is null;

          For this you need to make a join of target table with the source table.

          Hope it helps