Return Current Row from Table Only

[zulu1ghz]

Hi All,

I'm new to using peoplesoft and i'm trying to run a query against the job table.

I only want the query to look at the current row of data for each record, but I'm struggling as the query is returning results from all rows in the table for each record.

Does anybody know how I can restrict the query to current row only?

Thanks in advance

 

[mjldba]

This is where effdt & effseq come in handy but you have to do subqueries against the table (in this case PS_JOB) to return a single value for max(effdt) and max(effseq) as compared to TODAYS date.

Here's an example that should point you in the right direction ... good luck:

SELECT A.DEPTID, A.EMPLID, A.EMPL_RCD, B.NAME, A.POSITION_NBR, A.JOBCODE, A.EMPL_STATUS
FROM PS_JOB A, PS_PERSONAL_DATA B
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 <= TODAY)
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 = B.EMPLID
AND A.COMPANY = :1
AND A.DEPTID = :2
AND A.EMPL_STATUS = 'A'