Forum Stats

  • 3,838,643 Users
  • 2,262,385 Discussions
  • 7,900,715 Comments

Discussions

Categories

ODI Studio - filtering one table in mapping based on a value in another table

User_9ZAOX
User_9ZAOX Member Posts: 2 Blue Ribbon
in Data Integrator

I currently have a Mapping that filters incoming data pulling from an external source based on sysdate-3. In the unlikely event that we'd be down for more than 3 days, my team is working on adjusting the mapping to reference the last time the data was captured and looking a day before as the new replacement.

For right now, I'm trying to figure out how to filter a table (ex. TABLE_A by TABLE_A.LASTMODDIFIEDDATE) based on a date stored in a second table (TABLE_B). TABLE_B has a row for every extracted table, and the timestamp for when it was last successfully extracted. So, I want to filter the output basically like TABLE_A.LASTMODIFIEDDATE greater than or equal to TABLE_B.LAST_EXTRACT WHERE TABLENAME = X.

I have tried a bunch of things but I'm not certain how to make the mapping filter one table by another. There is no direct way to join these two tables. Any assistance would be helpful.

ODI Studio 12.2.1

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Rodrigo Radtke Souza
    Rodrigo Radtke Souza Member Posts: 212 Gold Badge

    The easiest way to do that is using a filter. Something like that:

    image.png

    Does that make sense?

    · Share on TwitterShare on Facebook
  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,640 Blue Diamond

    Not only a filter can do the job, but thinking in SQL (because that's what it is in the end), you could also use a JOIN with a condition only on the date (beware in not generating a cartesian product), or a condition with EXISTS or NOT EXISTS which can be done with a Subquery Filter component in your mapping (I know I did use that for an EXISTS in ODI 12c, but can't remember what version of 12c it was in case it's a component that has been added lately).

    · Share on TwitterShare on Facebook
  • User_9ZAOX
    User_9ZAOX Member Posts: 2 Blue Ribbon
    edited Mar 8, 2022 11:09PM

    TABLE_A.LASTMODIFIEDDATE greater then/equal to (SELECT LAST_EXTRACT FROM TABLE_B WHERE TABLENAME = 'TABLE_A')

    This is what I tried yesterday, and tried again today. As a result, when I ran it, I got the following error. We're filtering a SalesForce table with this attempt, so I don't know if it has to do with that?

    I wasn't quite certain what else to try. Thank you for the help so far.

    Error was:

    Caused By: java.sql.SQLSyntaxErrorException: [FMWGEN][SForce JDBC Driver][SForce]syntax error or access rule violation: unexpected token: FROM : line: 3

    Also, of note, I'm using a table from the target connection to filter the source connection when inputting into the target connection. Not certain if that's possible.

    · Share on TwitterShare on Facebook