I've been scratching my head over this one all day, and am very stuck.
This is the SQL:
SELECT papf2.full_name
, pah3.sequence_num seq
, pah3.action_code
, pha.SEGMENT1
FROM hr.per_all_people_f papf2
, po.po_action_history pah3
, po.po_headers_all pha
WHERE pah3.employee_id = papf2.person_id
AND pah3.object_id = pha.po_header_id
AND TRUNC(SYSDATE) BETWEEN papf2.effective_start_date
AND papf2.effective_end_date
AND pha.segment1 = 1413013
ORDER BY pah3.sequence_num DESC;
Which generates this output:
------------------------------------------------------------------
|FULL_NAME |SEQ |ACTION_CODE |SEGMENT1 |
------------------------------------------------------------------
|Geoffrey, Mr Geoffrey |6 |APPROVE |1413013 |
|Geoffrey, Mr Geoffrey |5 |RESERVE |1413013 |
|Geoffrey, Mr Geoffrey |4 |SUBMIT |1413013 |
|Mark, Mr Mark |3 |APPROVE |1413013 |
|Mark, Mr Mark |2 |RESERVE |1413013 |
|Geoffrey, Mr Geoffrey |1 |FORWARD |1413013 |
|Geoffrey, Mr Geoffrey |0 |SUBMIT |1413013 |
------------------------------------------------------------------
What I would like to do is to be able to remove this line from the SQL:
AND pha.segment1 = 1413013
And amend the SQL to find out which POs have been approved by the same person who raised the PO
To paraphrase, the SQL would do something like:
SELECT ...
FROM ...
WHERE
FULL_NAME IS THE SAME AS THE FIRST AND LAST VALUES FROM THE PO_ACTION_HISTORY table
But how I would do that?! I am way out of my depth.
Apologies for such a vague question.
Thank you