This content has been marked as final. Show 2 replies
I am not sure what you are looking to fetch. For the scenario you have given you need to use the sub-query.
SELECT A,SEGMENT1, A.APPROVED_DATE, PV.VENDOR_NAME
FROM (SELECT msib.segment1, MAX(PH.approved_date) approved_date
FROM PO_LINES_ALL PL,
WHERE PL.po_header_id = PH.po_header_id
AND MSIB.item_id = PL.ITEM_ID
AND MSIB.segment1 = :1
GROUP BY MSIB.segment1) A,
WHERE A.approved_date = PH.approved_date
AND PV.VENDOR_ID =PH.vendor_id
- The query should have right filters (item_id, vendor_id or document_num), otherwise it will behave very badly.
- They can return multiple recods if there are more than one PO approved at same time. In that case the outer query also needs an aggregation.
i already got it , put max(pha.approved_date) in the and part of the query