I wrote a query whereby I joined multiple tables to get one row per employee id and the relevant information for each employee job code, job code etc
SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID
FROM PS_NPS_VIEW_ALL A, PS_PERSON_NAME B, PSOPRDEFN E
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL 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_NPS_VIEW_ALL 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 = B.EMPLID
AND A.EMPLID = E.EMPLID
AND A.PER_ORG = 'EMP'
AND A.PAYGROUP NOT IN ('SUM','CWR'))
I wanted to get another field, JPM_DESCR90 into my query by joining the table PS_NPS_LIC_VW_RPT by EMPLID with one of my other tables in the original. I tried a left outer join between PS_PERSON_NAME B and PS_NPS_LIC_VW_RPT where by PS_PERSON_NAME B LEFT OUTER JOIN PS_NPS_LIC_VW_RPT C ON B.EMPLID = C.EMPLID
SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, C.JPM_DESCR90
FROM PS_NPS_VIEW_ALL A, (PS_PERSON_NAME B LEFT OUTER JOIN PS_NPS_LIC_VW_RPT C ON B.EMPLID = C.EMPLID ), PSOPRDEFN E
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL 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_NPS_VIEW_ALL 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 = B.EMPLID
AND A.EMPLID = E.EMPLID
AND A.PER_ORG = 'EMP'
AND A.PAYGROUP NOT IN ('SUM','CWR')
However I got multiple rows for each EMPLID since JPM_DESCR90 has multiple values for each EMPLID in PS_NPS_LIC_VW_RPT C .
I also tried a standard join between the two whereby B.EMPLID = C.EMPLID between
PS_PERSON_NAME B, and PS_NPS_LIC_VW_RPT C
SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, C.JPM_DESCR90
FROM PS_NPS_VIEW_ALL A, PS_PERSON_NAME B, PSOPRDEFN E, PS_NPS_LIC_VW_RPT C
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL 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_NPS_VIEW_ALL 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 = B.EMPLID
AND A.EMPLID = E.EMPLID
AND A.PER_ORG = 'EMP'
AND A.PAYGROUP NOT IN ('SUM','CWR')
AND B.EMPLID = C.EMPLID)
I'm tried to get JPM_DESCR90 from PS_NPS_LIC_VW_RPT into my original query so that I can get one value per EMPLID .I know that its probably a simple solution but would appreciate some help
Best Answer
I was able to solve my problem by adding the completion dates for JPM_DESCR90 (which is a field decription/name for certifications) in my query i.e. and then looked for max values of those dates in my query by adding MAX( TO_CHAR(C.JPM_DATE_3,'YYYY-MM-DD')) to the SELECT statement. This gives me the most recently acquired certification for each employee. I have attached a snapshot of my result as a jpeg image above
However, I have one more question. Is there a way to get all the certifications(JPM_DESCR90 aka Jobcodedescr column( in one row rather than in multiple rows for by getting each value of JPM_DESCR90 separated by ':' character for each employee irrespective of certification completion dates (C.JPM_DATE_3) ?