A project I am working on requires that I do some stuff on a iSeries/AS400 DB (which I'm accessing through a linked server on a remote machine).
I'm trying to build a query, but some of the AS400 syntax is giving me problems, and I was hoping that someone would be able to help me with this.
the table looks something like:
DEAL5 RO5 LINE5 RODTE5 VISIT5
--------- --------- ----- ---------- ------
5018 23054 1 20080826 H
5446 203714 1 20080821 H
5446 203714 1 20080825
Basically, I want the first line to show up, because it has the H, but I don't want the second or third line because the H on the second line has been resolved on the third line.
SELECT A.DEAL5, A.RO5, A.LINE5, A.RODTE5 (SELECT B.RO5 FROM NCLM5 B WHERE A.RO5=B.RO5 AND VISIT5='H' ORDER BY RODTE5 FETCH FIRST 1 ROWS ONLY) FROM NCLM5 A GROUP BY A.RO5
Nested selects are something that I have trouble with normally, and the unfamiliar syntax is just making it harder.