Help with Outer Join

[#1]

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

[Anonymous]

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 Henric Cronström is well worth a read, as are all his blog posts.

Bill

[phaneendra_kunc]

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;

[Anonymous]

Paneendra

Ah !! 

I reckon you have spotted the joker and are correct that what is needed is the Resident instead of the FROM 's.

[#5]

I tried submitting your recommended code (see below) and got the error below...

TableNew:

LOAD *
Resident Table1;
Outer Join (Table2)
Load * ;

SQL SELECT * From Table2;

ERROR:

Table Not Found.

Outer Join (TableNew)

Load *

Did I copy something wrong?  Thanks!

[phaneendra_kunc]

Yes... you are outer joining to a table that doesn't exists. Try below.

Outer Join (TableNew)

[#7]

My apologies...in my code above I put the wrong table name...I did, in fact, put TableNew in the Outer Join and got the same error.  I don't get it...if this code is creating a new table called TableNew by doing the Outer Join, why are we using that table name in the Outer Join statement?

[phaneendra_kunc]

Specifying table name Outer join will perform join with that table only. Some times you dont need to, but just for the sake of Good Scripting technique we use it.

The reason you are getting that error first load is a "LOAD *" so it might be concatenating to the original Table1. we have to use "NoConcatenate".

Try below.

OLEDB Connection String;
TABLE_NEW:

NoConcatenate

LOAD *

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

Outer Join (TABLE_NEW)

LOAD * ;

SQL Select * From TABLE2;