Outer Join

das_nirmalya · Post by **das_nirmalya** » Mon May 19, 2008 12:43 am

Want to implement SCD Type II in Server Version, and due to that i want to do Outer join what is the process.

chulett · Post by **chulett** » Mon May 19, 2008 6:35 am

Depends on the 'where'. In the database? SQL. Inside the job? Reference lookups are by definition an outer join. Would need more details to provide more help.

ray.wurlod · Post by **ray.wurlod** » Mon May 19, 2008 4:47 pm

Write out your processing specification (what is to happen in all cases) in English. You will find that this guides your thoughts.

For example, the generic Type 2 SCD needs to make two choices. Therefore there are two IF tests in the following pseudo code.

Code: Select all

If (row exists in target table)
Then
   If (change detected in appropriate columns(s)
   Then
      generate new SK value
      insert new row
      mark existing row in target as no longer current
   End
End
Else
   generate new SK value
   insert new row
End

I don't see a need for an "outer join" anywhere in that algorithm, except that a reference lookup is inherently an outer join.