what would the components needed and how do I transfer this query to be used in ODI?

Question

I am quite new with ODI (Oracle Data Integrator), learn how to do basic mapping and with filter, expression from source -> target tables but this time I have such query that I should transform it into mapping in Oracle.

WITH OUTER_QRY AS
(
SELECT T1.DATA_ID, T1.EMP_ID, T1.EMP_STORE, T1.HIRED_DATE, T1.EMP_STATUS
FROM L_EMPLOYEES T1
WHERE 1=1
  AND T1.DATA_CREATE_DATE_TIME IN (SELECT  MAX(DATA_CREATE_DATE_TIME) 
                            FROM L_EMPLOYEES T2 
                            WHERE T2.EMP_ID = T1.EMP_ID 
                              AND T2.EMP_STORE = T1.EMP_STORE
                              AND T2.HIRED_DATE = T1.HIRED_DATE
                            )
  AND T1.EMP_STATUS = 'REMOVED'
  AND T1.EMP_ID = '123456789' -- used to query smaller data as example
)
SELECT EMP_ID, EMP_STORE, HIRED_DATE, FILED_DATE
FROM L_EMPLOYEES LE
WHERE 1=1
  and (LE.EMP_ID, LE.EMP_STORE, LE.HIRED_DATE) NOT IN (SELECT EMP_ID, EMP_STORE, HIRED_DATE FROM OUTER_QRY)
  AND LE.EMP_STATUS = 'ACTIVE'
  AND LE.EMP_ID = '123456789' -- used to query smaller data as example
group by EMP_ID, EMP_STORE, HIRED_DATE, FILED_DATE
ORDER BY HIRED_DATE 
;

To start with, since we are using the same table two times and there is an outer query, I thought I can do something like joining the two tables then filter them with this as condition.

So the structure looked like two source table (same table) joined together with a join component -> filter -> expression -> target table

And below is what's in the first filter condition, which does give me exact solution as if I make query only for outer query

1=1
  AND L_EMPLOYEES_T1.DATA_CREATE_DATE_TIME IN (SELECT  MAX(L_EMPLOYEES_T2.DATA_CREATE_DATE_TIME) 
                            FROM L_EMPLOYEES_T1
                            WHERE L_EMPLOYEES_T2.EMP_ID = L_EMPLOYEES_T1.EMP_ID 
                              AND L_EMPLOYEES_T2.EMP_STORE = L_EMPLOYEES_T1.EMP_STORE
                              AND L_EMPLOYEES_T2.HIRED_DATE = L_EMPLOYEES_T1.HIRED_DATE
                            )
  AND L_EMPLOYEES_T1.EMP_STATUS = 'REMOVED'
  AND L_EMPLOYEES_T1.EMP_ID = '123456789' -- used to query smaller data as example

I thought in this way, I can plug in another filter between expression and target table so the expression can be used as outer query

1=1
  and (L_EMPLOYEES_T1.EMP_ID, L_EMPLOYEES_T1.EMP_STORE, L_EMPLOYEES_T1.HIRED_DATE) NOT IN (SELECT EMP_ID, EMP_STORE, HIRED_DATE FROM EXPRESSION)
  AND L_EMPLOYEES_T1.EMP_STATUS = 'ACTIVE'
  AND L_EMPLOYEES_T1.EMP_ID = '123456789' -- used to query smaller data as example

But the above doesn't work that expression isn't a table. I tried reading through the document of subquery filter component which I am not sure if it's the correct one to use but played around with it a bit and couldn't get it to work too because either no table found or invalid operation.

Does anyone has any suggestions / advise what should be the correct way to do this kind of mapping?

Thanks in advance for any suggestions.

user_3548287 · Answer

The subquery filter component would indeed be a solution for you. In the Properties pane, go on the Condition tab and enter a Subquery Filter Condition like this one :

    NOT IN (
        SELECT EMP_ID, EMP_STORE, HIRED_DATE FROM FROM L_EMPLOYEES T1
            WHERE 1=1
            AND T1.DATA_CREATE_DATE_TIME IN (SELECT  MAX(DATA_CREATE_DATE_TIME) 
                            FROM L_EMPLOYEES T2 
                            WHERE T2.EMP_ID = T1.EMP_ID 
                              AND T2.EMP_STORE = T1.EMP_STORE
                              AND T2.HIRED_DATE = T1.HIRED_DATE
                            )
            AND T1.EMP_STATUS = 'REMOVED'
    )

another way to do it is to use the sql override method :

Drag and drop your L_EMPLOYEES datastore only once on the canvas and connect it to your target.
Go on the Physical tab and click the source datastore
In the Properties pane, go on the Extract Options tab
Set your entire query (as posted in your question above) as the value for CUSTOM_TEMPLATE option

There is an example in this blog post : https://blogs.oracle.com/dataintegration/odi-12c-sessionizing-data-with-matchrecognize-and-sql-override

This method should not be abused as it makes it less visual in ODI so harder to maintain. But it is a great tool to use when the query is complex as this is the case here.

what would the components needed and how do I transfer this query to be used in ODI?

1 Answers