Queries - Reg to Temp

[tcimis]

I have the following issue:

I need to write a query using the PeopleSoft query tool (don't have access to SQLPlus) to track employees changing from regular to temp status.
Need to report emplid, name and effdt of change.

I need to do the same thing to track name changes.
Need to report emplid, name, previous name and effdt of change.

Any help would be greatly appreciated.

 

[tcimis]

I was able to figure out a way to do this.

The only issue I have now is outer join to email addresses or a "does not exist" subquery for email addresses. I don't seem to be able to get either one to work.

 

[notadba]

Hi tcimis,

Can you post the sql - there is a probably an inner join invalidating your outer join.

 

[tcimis]

Here is the sql. I now have it returning all the records but I need it to return the actual email address.

SELECT A.EMPLID
FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1
WHERE A.EMPLID = A1.EMPLID
AND A.EMPL_RCD = A1.EMPL_RCD
AND A1.OPRID = '4'
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND A.EMPLID NOT LIKE 'X%'
AND A.HR_STATUS = 'A'
AND A.EMPLID NOT IN (SELECT B.EMAIL_ADDR
FROM PS_EMAIL_ADDRESSES B, PS_PERALL_SEC_QRY B1
WHERE B.EMPLID = B1.EMPLID
AND B1.OPRID = '4'
AND ( B.E_ADDR_TYPE = 'BUSN' )) )

Thank you!

 

[notadba]

I think I understand the logic - but your 'not in' criteria is quite right - as you are comparing an emplid to an e-mail address to find the mismatch - so will return all rows. Your subquery should start with "AND A.EMPLID NOT IN (SELECT B.EMPLID...."

If all employeees have a non-business e-mail address, then this can be a simple query - just do an inner join against the PS_EMAIL_ADDRESSES and return the address as one of your columns in the select.

If you need to cater for employees without e-mail addresses, thent his can be either done with a union query or an outer join.

I havent got a version 8 or 9 running here so at the moment so can't show you how it would create the sql - but the basic logic would be (with query security records stripped...

SELECT A.EMPLID
, B.E_ADDR_TYP
, B.B.EMAIL_ADDR
FROM PS_JOB A LEFT OUTER JOIN PS_EMAIL_ADDRESSES B
ON A.EMPLID = B.EMPLID
AND B.E_ADDR_TYP <> 'BUSN'
WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT)
FROM PS_JOB A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ)
FROM PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND A.EMPLID NOT LIKE 'X%'
AND A.HR_STATUS = 'A'

Let me know how you go.

 

[tcimis]

Here is the SQL when I try an outer join. The query result set is to large - it returns over 900000 rows. I have approx 8175 employees I'm trying to pull. I need to return email addresses and not everyone has their email address in PS.

SELECT A.EMPLID, B.EMAIL_ADDR
FROM (PS_JOB A LEFT OUTER JOIN PS_EMAIL_ADDRESSES B ON A.EMPLID = B.EMPLID AND B.E_ADDR_TYPE = 'BUSN' ), PS_EMPLMT_SRCH_QRY A1, PS_PERALL_SEC_QRY B1
WHERE A.EMPLID = A1.EMPLID
AND A.EMPL_RCD = A1.EMPL_RCD
AND A1.OPRID = '49534'
AND (B.EMPLID = B1.EMPLID OR B.EMPLID IS NULL )
AND B1.OPRID = '49534'
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND A.EMPLID NOT LIKE 'X%'
AND A.HR_STATUS = 'A' )

Thanks for your assistance.

 

[notadba]

Some ideas... Are you using concurrent jobs? Are you getting multiple rows back for each empl_rcd# occurrence? Can you change your driving table from PS_JOB to PS_EMPLOYEES to get around some of the processing?

Is the data you are getting out correct? As you are on Oracle, you can limit the rows being returned to do a sample - (doing this from memory so hope it works)
Add a criteria - left hand expression "rownum < 500 and 1"
operand "="
right side expression "1"

This forces in a criteria that will limit the row return to whatever you set the number to - but you may run into a little trouble with the bracketing that has been introduced by the query security records...

One of the other problems that is in the query is something introduced by the security record PS_PERALL_SEC_QRY - the line AND (B.EMPLID = B1.EMPLID OR B.EMPLID IS NULL ) will actually invalidate the outer join and turn it to an inner join...

We seem to have a lag in response times - assuming it is geographic - I am in Australia, where are you?

 

[tcimis]

I switched to PS_EMPLOYEES which maxes out the query result same as PS_JOB. I placed in the criteria an EMPLID I know has an email address and 2 that don't. I received one record back for the one with an email address and 24143 records for each EMPLID that did not have an email address. PS_EMPLOYMENT contains 24143 records. PS_EMPLOYEES contains 8183 records. I am getting a cartesian product for some reason.

Not sure what you mean by concurrent jobs.

Limiting the rows returned the number of rows I specified but I got duplicates for some EMPLID's and none for others.

I am located on the East Coast of the US.

 

[notadba]

OK. The PS_EMPLOYEES should only contain active people - so the numbers are probably right.
By concurrent jobs - I was referring to when you have a person with multiple active empl_rcd's which may have been contributing to your extra rows coming back - since there is no join in your query between the Job/Employees and the PS_EMAIL_ADDRESSES table (don't have an instance available - so not able to check the table structure).

It may be that you are getting duplicates - either from a key not being joined (e.g empl_rcd) - or it is the query security records causing the issue. While it is a personal hate of mine, you can use the 'distinct' option - which is sometimes the only way around this issue once you have a few security tables added in.

With the row limitation, you will be missing entries for some emplids unless you have a sort option - it will just return the first x rows it finds - but it can be handy for sampling for data.

Will try to track down my v9 image to try to replicate your query..