7 Replies Latest reply: Oct 27, 2014 7:07 PM by Phaneendra Kunche RSS

    Help with Outer Join

      Earlier in my script I created a table called TABLE1.   I want to perform an Outer Join with TABLE2, which has not yet been pulled, and which resides in an OLEDB connection.  I cannot get this to happen (basically don't know how), so I thought I’d first bring in this TABLE2 separately, then perform the Outer Join in a subsequent step.

       

      So now I have two tables, TABLE1 and TABLE2.   What’s the best way to do an Outer Join on field = PNumber?

       

      I tried doing this with no success:

       

      LOAD * FROM TABLE1;Outer Join LOAD * FROM TABLE2;

       

      I get an error saying that these tables cannot be found.  I’m relatively new to joins, so any help is much appreciated.

       

      Thanks,  Dan

        • Re: Help with Outer Join
          Bill Markham

          Dan

           

          Bit of a guess, but do TABLE1 and TABLE2 have the same fields ?  If so then maybe the second load of TABLE2 may have auto concatenated onto TABLE1 and not actually created TABLE2.

           

          If my guess is wrong then could you share the log file from the load ? 

           

          Or more of the load script, especially including the loads of TABLE1 & TABLE2 and a few lines after the outer join ?

           

          This blog post Joins and Lookups by hic is well worth a read, as are all his blog posts.

           

           

          Bill

          • Re: Help with Outer Join
            Phaneendra Kunche

            Try below and Chnage your OLEDB and Table information accordingly



            OLEDB Connection String;
            TABLE_NEW:

            LOAD *

            Resident TABLE1; //Since this table already exists in your model

            Outer Join (TABLE_NEW)

            LOAD * ;

            SQL Select * From TABLE2;