Nested Selects
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.
Software/Hardware used:
Software/Hardware used:
ASKED:
October 8, 2008 3:47 PM
UPDATED:
October 8, 2008 4:18 PM
Answer Wiki:
You could try something like this.
The Subselect picks the list that you don't want so you use the NOT IN (the list)
SELECT A.DEAL5, A.RO5, A.LINE5, A.RODTE5 FROM NCLM5 A
where A.Visit5 = 'H' and A.R05 not in
(select A.R05 from NCLM5 where visit5 = ' ')
I believe that this is the same SQL command that you would use in any other database.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
