PS 9.1 - Query tool issue (Outer join w/ Effdate)

[QTNC]

I've exhausted my efforts and seeking some assistance with this PS Query Tool (v 9.1) issue.

I am attempting to create a outer join between a table with user names and security roles (UM_IDM_HCMROLES) and a JOB table (UM_JOB_FERPA_VW).

Issue: there are several users in the HCMROLES table that do not have job records (therefore, do not exist in the JOB_FERPA table). I need all records from the HCMROLES table and any additional fields that exist for users that do have JOB_FERPA data available. Sounds easy enough, right?

What I have so far: I created a left outer join, and also added criteria to handle the null effdate from the JOB_FERPA table. But to date I've been unsuccessful in pulling system users that do not have job records. It seems the PS tool is creating some sort of subquery to deal with the effDate, but if I remove the effdate criteria, I receive an error.

Following is the SQL statement for review. Note: I do not have access to manually update the SQL statement, I can only manipulate it through the tool. =S

Any thoughts/suggestions are very much appreciated.

Cheers,
Nancy

-----------



SELECT A.EMPLID, A.FIRST_NAME, A.LAST_NAME, A.MIDDLE_NAME, A.GROUP_NBR, B.BUSINESS_TITLE, B.EMPL_STATUS, TO_CHAR(B.EFFDT,'YYYY-MM-DD'), B.DEPTID, B.BUSINESS_UNIT
FROM PS_UM_IDM_HCMROLES A, PS_UM_JOB_FERPA_VW B
WHERE B.EMPLID = A.EMPLID(+)
AND ( B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_UM_JOB_FERPA_VW B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SYSDATE)
OR B.EFFDT IS NULL)
AND A.EMPLID LIKE :1

 

[notadba]

Hi Nancy,

One of the joys of PSQuery. Outer joins do not work well when you need max effdt/effseq subqueries on the outer join table.

Probably the simplest way to do this is leave the current query in place (or convert it to an inner join) and add a union and return the rows from the PS_UM_IDM_HCMROLES where emplid does not exist in PS_UM_JOB_FERPA_VW. Untested code below as I only have access to a SQL server and DB2 version...

SELECT A.EMPLID
, A.FIRST_NAME
, A.LAST_NAME
, A.MIDDLE_NAME
, A.GROUP_NBR
, B.BUSINESS_TITLE
, B.EMPL_STATUS
, TO_CHAR(B.EFFDT,'YYYY-MM-DD')
, B.DEPTID
, B.BUSINESS_UNIT
FROM PS_UM_IDM_HCMROLES A
, PS_UM_JOB_FERPA_VW B
WHERE B.EMPLID = A.EMPLID(+)
AND ( B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_UM_JOB_FERPA_VW B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SYSDATE)
OR B.EFFDT IS NULL)
AND A.EMPLID LIKE :1

union

SELECT C.EMPLID
, C.FIRST_NAME
, C.LAST_NAME
, C.MIDDLE_NAME
, C.GROUP_NBR
, ' '
, ' '
, ' '
, ' '
, ' '
FROM PS_UM_IDM_HCMROLES C
WHERE C.EMPLID = :1
AND NOT EXISTS(SELECT 'X'
FROM PS_UM_JOB_FERPA_VW D
WHERE C.EMPLID = D.EMPLID )

 

[QTNC]

I did see this briefly mentioned in another thread, however I am new to query so I'm unable to figure out how to make this work.

On a simple level, I tried to create a query to pull all records from HCMROLES that do not exist in JOB_FERPA, with no success.

Following is the SQL: (0 results)


SELECT A.EMPLID, A.FIRST_NAME, A.LAST_NAME, A.MIDDLE_NAME, A.GROUP_NBR
FROM PS_UM_IDM_HCMROLES A
WHERE NOT EXISTS (SELECT B.EMPLID
FROM PS_UM_JOB_FERPA_VW B)
AND A.EMPLID = :1


Thoughts ?

 

[notadba]

Hi,

In your not exists subquery, you should not be slecting any columns. Remove the selected field (B.EMPLID ) and instead create a join criteria on the B.EMPLID = A.EMPLID in the subquery.
That should work.

 

[QTNC]

Brilliant ... it worked. Although I've tweaked the query since and added more criteria, I wanted to share the SQL for other that experience similar issues:




SELECT A.EMPLID, B.BUSINESS_UNIT, B.EMPL_STATUS, B.BUSINESS_TITLE, B.DEPTID, A.FIRST_NAME, A.LAST_NAME, A.MIDDLE_NAME, A.GROUP_NBR, E.EMAIL_ADDR
FROM PS_UM_IDM_HCMROLES A, PS_UM_JOB_FERPA_VW B, PS_EMAIL_FERPA_VW E
WHERE B.EMPLID = A.EMPLID
AND A.EMPLID = E.EMPLID(+)
AND B.BUSINESS_UNIT = 'UMBOS'
AND ( A.GROUP_NBR LIKE 'FS%'
OR A.GROUP_NBR LIKE 'SUMMIT_EXE%'
OR A.GROUP_NBR LIKE 'SUMMIT_FDM%')
AND ( E.EMAIL_ADDR LIKE '%@umb.edu'
OR E.EMAIL_ADDR IS NULL)
UNION
SELECT C.EMPLID, ' ', ' ', ' ', ' ', C.FIRST_NAME, C.LAST_NAME, C.MIDDLE_NAME, C.GROUP_NBR, F.EMAIL_ADDR
FROM PS_UM_IDM_HCMROLES C, PS_EMAIL_FERPA_VW F
WHERE NOT EXISTS (SELECT D.EMPLID
FROM PS_UM_JOB_FERPA_VW D
WHERE D.EMPLID = C.EMPLID)
AND C.EMPLID = F.EMPLID(+)
AND ( C.GROUP_NBR LIKE 'FS%'
OR C.GROUP_NBR LIKE 'SUMMIT_FDM%'
OR C.GROUP_NBR LIKE 'SUMMIT_EXE%')
AND ( F.EMAIL_ADDR LIKE '%@umb.edu'
OR F.EMAIL_ADDR IS NULL)
ORDER BY 1, 9