Using JOB record in an QUERY MANAGER outer join. Need help.

[commish252]

Within Peoplesoft Query Manager, I need to extract PSOPRDEFN fields OPRID and EMPLID, and if a JOB record is found extract DEPTID and EMPL_STATUS. Some EMPLID fields in the PSOPRDEFN are null. Research showed that I cannot do an outer join on a table that is effective-dated, and JOB is. Can anyone give me an alternative? Thanks.

 

[notadba]

Hi commish252,
Do you need to use JOB? Are you running the employees refresh process - if so PS_EMPLOYEES will have the current snapshot for all active employees removing the need for the effective date logic.

 

[commish252]

Thanks, but I still get an automatic Max EFFDT criteria added within the Query Manager when I use PS_EMPLOYEES and it doesn't change my results. Any other ideas?

 

[notadba]

You can delete the max effdt/sequence criteria as it serves no purpose against this table/materialised view.