Performing Outer join in Server jobs

bobthebuilder · Post by **bobthebuilder** » Fri Nov 04, 2005 4:07 pm

Any suggestions in performing outer joins in Server jobs other than defining a user defined query

--Bob

Bala R · Post by **Bala R** » Fri Nov 04, 2005 5:27 pm

Reference lookups using OCI, ODBC or Hashfiles. Do a search for outer join.

ray.wurlod · Post by **ray.wurlod** » Sat Nov 05, 2005 2:55 am

Anything in the Table Name field becomes the FROM clause in the SELECT statement in Generated SQL.
You can quite happily put things like

#Schema#.Employee T1 LEFT OUTER JOIN #Schema#.EmployeeDependent T2 ON T1.EmployeeID = T2.EmployeeID

into the Table Name field. If you use aliases like that, make sure that the Derivation column in the Columns grid uses the same aliases.

rleishman · Post by **rleishman** » Sun Nov 06, 2005 7:09 pm

The SQL Builder in 7.5.1A - despite its problems ( viewtopic.php?t=96324&highlight=) - does have a marked improvement in producing outer joins.

ray.wurlod · Post by **ray.wurlod** » Mon Nov 07, 2005 2:25 am

Neglected to mention that the Merge stage performs any kind of key-based join between two text files.