Forum Stats

  • 3,816,315 Users
  • 2,259,170 Discussions


ODI: Select fields after a JOIN


I think it has a simple solution, but I have been unable to find the correct component to use in this situation.

I'm working in a Mapping with multiple tables to be joint in a final target table.

These tables have a lot of fields, and after using the join component on them, I would like to select only a few fields, to simplify the process and the next steps. Which is the correct way to achieve this?

I have been trying to use the SET and SUBQUERYFILTER components, but these expect two inpunts, and I would like to use only withe the join component as an Input.

Thanks for your time in advance.


  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,539 Blue Diamond
    edited Jan 25, 2021 4:25PM


    While not being an ODI expert at all, I did use an Expression component for that kind of needs. Selecting a subset of columns or also rename some, without really using any real SQL expression to transform those columns (just to make the rest of my mapping "cleaner" and easier having only a limited list of columns exposed).

    SET and SUBQUERYFILTER are used for very different things than what you look for.

    Guillem Caceres
  • Thanks for your response Gianni.

    In fact, EXPRESSION component allows choosing some fields and makes the mapping visually more clear, but it keeps maintaining every field not selected during the next steeps.

    Is there a solution to dismiss every unnecessary field on the following mapping steeps in order to make the process more efficient?

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,539 Blue Diamond

    I don't have my ODI sandbox in front of me right now, but if I remember correctly the EXPRESSION component only shows you the columns you defined inside it for the following steps.

    Just ignore the source being the component you used as input for the EXPRESSION (because that one is also available as the EXPRESSION is almost like a "transparent" component not doing much).

    Anyway, I'm not aware of other ways of doing it. EXPRESSION was good enough in my mappings to filter and "conform" the columns to make the next steps easier to build.

    Guillem Caceres
  • It's true that it only shows you the fields selected, but in the back it keeps all the other fields for next steps. For example, if you connect an EXPRESSION component following to the EXPRESSION we were talking about (the one after a JOIN) it will show every field from the two tables joined, not only the fields selected on the previous EXPRESSION. I'm not sure if I'm explaining myself.

    Anyway, EXPRESSION is a good visual solution, and I have not seen another option to select fields on a mapping.