Outer Join Not Working-Enterprise/Autolink

[MHurdatADP]

I'm joining JOB to AL_ALA_DATA. I want everyone's job data, even if they do not have an entry in AL_ALA_DATA. However, when I add AL_ALA_DATA and link it to JOB, I lose the unmatched records, even though I have checked "show unmatched records" for both file number and paygroup links from JOB to AL_ALA_DATA. Is this because there are two key fields in AL_ALA_DATA? , or because AL_ALA_DATA is also effective-dated? Both? Any ideas?

 

[CharlesCook]

It's because AL_ALA_DATA is also effective dated. In effect you are asking for the max dated (for example) record that does not exist. You should be able to do a "OR" critera that says max effective dated AL_ALA_DATA OR AL_ALA_DATA.Effdt is Null. I don't have access to a ADP system right now or I would try to put it together.

If you remember we tried this in the past and could not get it to work.

http://tek-tips.com/viewthread.cfm?qid=1174051

I'm not sure what to tell you to do as a work around. The next time I do an ADP job I will write up an article on this.



CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[MHurdatADP]

Thanks, Charles. That did it. I changed the criteria from "effective date of AL_ALA_DATA is report's effective date" to a list, where ANY can be true, either 1. "effective date....." or 2. effdt is null. Thanks, again.

 

[CharlesCook]

Cool, can you email me a screen print of your selections.

Charles AT CharlesCook.com

C
Thanks

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[MHurdatADP]

I'm going to email you a BMP file of it.