Geeks on Campus

This Changes Everything!

A colleague just taught me how to left-outer-join (LOJ) an effective dated table in PeopleSoft Query.  In the past I would only be able to LOJ non-effective dated tables or use views that had the effective date logic built in.  I just looked through my old posts and it doesn’t look like I’ve posted about LOJs before.  So I’ll give a quick rundown of why you would want to use one.

Example

Say you have three different tables for First, Middle, and Last names.  If you just standard joins you will only see results for those records that are present in all three tables.  But what about my dad?  My dad doesn’t have a middle name.  Weird, I know.  Here is some sample data.

So using standard joins and connecting these tables on UID, you would get:
1 John Isa Coolguy
2 Larry The Cat

What about Joanne?  That’s right, JK Rowling also has no middle name.  She gets left out because of the standard join.  However, using a left-outer-join would allow you to make sure that she and any others were not left off.  And that output would look like this:
1001 John Isa Coolguy
1002 Larry The Cat
1003 Joanne  Rowling

Cool, but how?

In PeopleSoft Query, when you go to join a record you will get a “Join Type” box.  Typically you would select Standard Join “Join to filter and get additional fields.  To do an LOJ, you would select the other option “Join to get additional fields only” (Left outer join).  An important thing to remember is that if you need to put any criteria on the LOJed table, you need to specify that the criteria belongs to the ON clause of the outer join in the Edit Criteria Properties box.

So that is how to do a normal LOJ, which I was already comfortable with.  The extra complication is with effective dated tables.  Those tables come with special criteria which is also a subquery.  It isn’t as simple as saying that criteria “belongs” to the left outer join like any other regular criteria.  The magic to make this work is to leave that effective date criteria but then to manually add an additional criteria for the effective date and set the condition type to “is null”.  Then change the logical setting from AND to OR and group those two lines with parenthesis.  As such…

I can only assume that I’ve made your life 1,000% better. Well, we both have Jeff M. to thank. He’s the one that taught it to me.  Let me know in the comments if you have any questions about how to make this work.

Cheers.

-John