3 Replies Latest reply on Sep 30, 2020 7:59 AM by Nico Heinze

    Join multiple tables

    Christine Choy Active Member

      Hi, I would like to join around 10 tables and write to the targeted table.

       

      I'm using Joiner transformation, I need to create 9 joiners in order to achieve it. I would like to ask the experts here, any other options if not using Joiner transformation?

       

      PS: I found some article recommend to use "Source Qualifier" transformation, but I couldn't find in the list of transformation, is this discontinued already?

       

       

      Thank you!

        • 1. Re: Join multiple tables
          Nico Heinze Guru

          First I hope you're asking about PowerCenter and not the Informatica Platform (e.g. IDQ), the latter works differently.

           

          In PowerCenter, whenever you drag a source definition into a mapping, not only the source definition itself will be included in the mapping but also a so-called Source Qualifier transformation (SQ) will be created. This SQ allows (for relational sources) e.g. to define your own filter conditions or to even enter a whole SQL query (a feature which I use as rarely as possible, but that's a long story).

          The SQs for each possible kind of source systems (e.g. SAP tables, relational databases, mainframe files, text files in CSV format, and many more) have their special properties. For example, in the SQ for a flat file you can define several parts of the exact file format; in a SQ for relational tables, you can define in which database schema the respective table is stored; and so on. But that's not the point here, just additional explanation.

           

          Now the nice thing about a relational SQ is that in certain cases you can define a so-called "common source qualifier". This is a SQ which extracts data from more than one table. You create the SQ itself by dragging the source definition into the mapplet / mapping designer tool; and then you simply add all the additional source definitions, delete their respective SQ transformations, and drag from each source definition all ports into the SQ of the first source definition which you need downstream.

          Of course in this case you have to make sure that there are some "logical links" between all those source tables defined. You can for example do this by entering your own hand-coded "user-defined join" between all those source tables, but in most cases PowerCenter can create the respective SQL statements including all those parts on its own.

           

          In case this was too much explanation, may I ask how much you understand about PowerCenter in general. The concept of Source Qualifiers is so basic that it's taught during the first day of each PowerCenter course (otherwise the course is not worth its money). All the things I've written above are nothing but absolutely basic stuff in PowerCenter. It is surprising for me to see that a PowerCenter developer does not know how to create a SQ, so please don't be offended by my curiosity.

           

          Regards,

          Nico

          • 2. Re: Join multiple tables
            Christine Choy Active Member

            Thanks for your long reply, I'm actually asking for IDQ purpose.

            • 3. Re: Join multiple tables
              Nico Heinze Guru

              Ah, ok, that's indeed a completely different question. May I suggest that you post your question on the Data Quality forum, there you will find the right experts.

               

              Good luck and regards,

              Nico