×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

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 )

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

(OP)
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 ?

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

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.

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close