I've tried a lot of google-fu, but can't quite find the solution I'm looking for. Most are just too advanced for my scenario. I'm also a newb with SQL, so I apologize for the newbiness of this question.
This is for Oracle 10g.
PS_JOB employees have a unique EMPLID, but their FILE_NBR can change. I need to return the EMPLID of each EE that has a FILE_NBR change. This is a small part of a larger query that joins PS_JOB with several other tables, so please be specific where the solution should be put: in the main select statement, the join, or with the other where clauses.
For a simple table example of PS_JOB
EMPLID FILE_NBR Action Date
0005 12345 Hire 01/01/2013
0005 67890 term 04/05/2015
0006 55555 Hire 02/05/2014
0006 55555 term 04/15/2015
Select a.paygroup, a.EMPLID, a.FILE_NBR, c.name, e.TLM_STATUS, b.rehire_dt as "Emplmnt_Rltd_Dtes-Rehire Date",
WHEN a.ACTION_REASON = 'REH' THEN 'Y'
END -- confirms if EE is true rehire
FROM PS_EMPLOYMENT b --no effective dated rows
right outer join SYSADM.PS_JOB a
on a.emplid = b.emplid
right outer join SYSADM.PS_PERSONAL_DATA c -- no effective dated rows
on a.emplid = c.emplid
right outer join SYSADM.PS_SMS_SUBSCRB_TBL d
on a.paygroup = d.SUBSCRIBER_ID
right outer join PORTAL.PS_TS_EMPL_TLM_STATUS e
on a.emplid = e.emplid
b.rehire_dt IS NOT NULL
--and a.EFFDT = (select max (a2.effdt) from SYSADM.PS_JOB a2 where
a.EMPLID = a2.EMPLID)
and d.EFFDT = (select max (d2.effdt) from PS_SMS_SUBSCRB_TBL d2 where
d.SUBSCRIBER_ID = d2.SUBSCRIBER_ID)
and e.EFFDT = (select max (e2.effdt) from PORTAL.PS_TS_EMPL_TLM_STATUS e2
where e.emplid = e2.EMPLID)
and d.EFF_STATUS <> 'A'
--and a.action <> 'REH'
--and a.ACTION_REASON <> 'REH'
--and b.rehire_dt = a.EFFDT
--and b.emplid = '50731/246'
order by a.paygroup, a.emplid
SELECT distinct JOB.EMPLID, JOB.PAYGROUP, PER.NAME, EMP.REHIRE_DT as "Empl_Rlated_Dates_Rehire_Date", SUB.EFF_STATUS as "Paygroup Status",TLM.TLM_STATUS,
WHEN JOB.ACTION_REASON = 'REH' THEN 'Y'
END as "True Rehire?", -- confirms if EE is true rehire
CASE -------------------------------Adam's code
WHEN EXISTS(SELECT 1
FROM PS_JOB otherJobs
WHERE otherJobs.EMPLID = JOB.EMPLID
AND otherJobs.FILE_NBR <> JOB.FILE_NBR) THEN 'Y'
END as "Had FN Change?"
FROM SYSADM.PS_EMPLOYMENT EMP
INNER JOIN SYSADM.PS_JOB JOB
ON EMP.EMPLID = JOB.EMPLID
INNER JOIN SYSADM.PS_PERSONAL_DATA PER -- no effective dated rows
on JOB.emplid = PER.emplid
INNER JOIN SYSADM.PS_SMS_SUBSCRB_TBL SUB
on JOB.paygroup = SUB.SUBSCRIBER_ID
INNER JOIN PORTAL.PS_TS_EMPL_TLM_STATUS TLM
on JOB.emplid = TLM.emplid
JOB.EMPLID = '50731/246'
and EMP.REHIRE_DT IS NOT NULL
and JOB.EFFDT = (select max (JOB2.effdt) from SYSADM.PS_JOB JOB2 where
JOB.EMPLID = JOB2.EMPLID)
and SUB.EFFDT = (select max (SUB2.effdt) from PS_SMS_SUBSCRB_TBL SUB2 where
SUB.SUBSCRIBER_ID = SUB2.SUBSCRIBER_ID and SUB2.EFF_STATUS = 'A')
Some remarks; most people use
LEFT JOINs instead of
RIGHT, because they find them easier to read. It may be good practice to get in that habit. Also, use a consistent spacing style so that it's easy to read what you're doing. I align my keywords on one side and my query info on the other.
This query will give you all
EMPLID where there are at least two different
SELECT PS_JOB.EMPLID FROM PS_JOB GROUP BY PS_JOB.EMPLID HAVING MIN(PS_JOB.FILE_NBR) <> MAX(PS_JOB.FILE_NBR)
However, I'm not sure how you want it integrated into your query, that depends on what you want. If you want to only select entries with changes, you'll need to add something like
AND EXISTS(SELECT 1 FROM PS_JOB otherJobs WHERE otherJobs.EMPLID = a.EMPLID AND otherJobs.FILE_NBR <> a.FILE_NBR)
If you just want it in your select, you can simply put that exists in a case statement, indicating the existence however you want.
CASE WHEN EXISTS(SELECT 1 FROM PS_JOB otherJobs WHERE otherJobs.EMPLID = a.EMPLID AND otherJobs.FILE_NBR <> a.FILE_NBR) THEN 'Y' ELSE 'N' END