Last information based on max date

G

gmore

Hello!

I have this sql query below. It works fine, but not exactly as I would like
to. I read some good discussions before I decided to post this message.
However, in my case it's not only 2 tables, but 3. Here's my concern, I would
like to obtain the very last information based on max date... I don't know
what I did wrong, it will show all the rows for the item 1010738... Somebody
can see what's wrong with it?

Thanks for your help!

select
Q.po_header_id,Q.item_id, W.SEGMENT1, W.creation_date

from
PO.PO_HEADERS_ALL W INNER JOIN
(PO.PO_LINES_ALL Q INNER JOIN
(
select
PLA.PO_HEADER_ID,PLA.ITEM_ID, MAX(PHA.CREATION_DATE) AS S
from
PO.PO_LINES_ALL PLA INNER JOIN PO.PO_HEADERS_ALL PHA ON
(PLA.PO_HEADER_ID = PHA.PO_HEADER_ID)
group by
PLA.PO_HEADER_ID,PLA.ITEM_ID
) T ON (Q.PO_HEADER_ID=T.PO_HEADER_ID) AND (Q.ITEM_ID=T.ITEM_ID))

on (W.CREATION_DATE = T.S) AND (W.PO_HEADER_ID = Q.PO_HEADER_ID)

where Q.ITEM_ID = 1010738;
 
Top