1 Reply Latest reply on Aug 7, 2018 2:33 AM by Nico Heinze

    how to use Souce Qulifier joiner

    siva kumar Active Member

      i am informatica beginer ,please eloborate me source qulifier join operation,

       

      i have some questions

       

      i have a 5 tables like

      emp table,

      departments table,

      jobs table,

      locations table,

      countries table

       

      all are define primary key and forignkey relationship

      questions:

      1.i can join these 5 tables using joiner,lookup transformation,

      ------->but how can i join these 5 tables using source Qulifier

      doughts:

      (a).how can i join source qulifier sql override (sql query) not any other part touch.its possibe please explain

      (b).its complesary ! all table join key column participate sql quries select statement

      (c).my sorce emp_name column is two(first_name,last_name) my target as only one name port

           i concat first and last name. how can i pass this concat port into target . this concat expression participate           sql select statement.

       

       

      i attached my map here please check

      .

        • 1. Re: how to use Souce Qulifier joiner
          Nico Heinze Guru

          You can simply drag all attributes from the 5 tables into one single Source Qualifier which retrieves those attributes from the 5 different source definitions and delivers them downstream.

          The only question is whether PowerCenter "knows" the PK/FK relations; if so, PowerCenter can create the join part of the SQL statement on its own; if not, you will have to provide the join condition in the field "User-Defined Join".

           

          Or (as you already did) you create a full SELECT statement as the "SQL Query". Not my favourite approach for several reasons, but well, as long as it works, what the heck.

          The most important thing about the SQL Query that you must pay attention to: make sure that the SELECT list (meaning the attributes in SELECT a1, a2, a3...) all exist in the Source Qualifier as port names (in this case, a1, a2, a3...) and that ALL these ports are connected to some downstream transformation.

          This is one of the big disadvantages of the SQL Query: if you need one more or one less connected port, you always have to adapt your SQL query. Very annoying, very time-consuming, very error-prone.

           

          If that's not yet clear enough, please ask again, then I can post a screenshot of a SQ with three input tables so that you can see what I mean.

           

          Regards,

          Nico