1 Reply Latest reply on Jun 9, 2014 12:54 PM by sinan_gg

    About ODI Filtering


      Hello everyone,


      I have a question. I am creating a mapping / interface on ODI 12c. I have some tables to join and conditions belongs to each. One table has such a condition;


      join   ra_customer_trx_all ct  on ctl.customer_trx_id = ct.customer_trx_id

                                      and ct.org_id=23

                                     and ct.batch_source_id  not in (select batch_source_id

                                                                      from   ra_batch_sources_all

                                                                      where  name like '%KHÇ%')

      There is a subquery condition. How can I model it on ODI. In the filter window, only main table exists. In order to take batch_source_id column from ra_batch_sources_all table, shoul I join or how?



        • 1. Re: About ODI Filtering



          You can achieve this condition with a few different method on ODI 12c.


          1. You can use Temporary Interface (Yellow Interface) to create subselect queries. After this, simply to filtering your structured data. This link will help you how to use temp interface ;  http://oracledataintegratorcommunity.blogspot.com.tr/2013/04/create-temporary-interface-in-oracle.html

          2. Main and subquery table (in this example ra_batch_sources_all and ra_customer_trx_all) will be joined with left outer method in source datastore by using filtring column (ra_customer_trx_all.batch_source_id  = ra_batch_sources_all.batch_source_id ). After this, in target datastore, you can add a new column called "flg", and if a record in ra_customer_trx_all but not in the ra_batch_sources_all, flg value set to "1", otherwise flg value set to be "0".

          After mapping the target datastore columns, you can filtering the "flg" column by using flg = 1 condition. This result is same above solution.

          3. This is the last solution, you can use hard-coded filtering in source datastore.

          Hopefully, there has been helpful for you,